April 16, 2008 at 1:37 pm
So how does scorecard relate to CoursePar? The table description you posted earlier is apparently not accurate. The error implies that scorecard does NOT have a courseID field.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 1:38 pm
Except for a little reformating of code (I made NO changes to the actual logic) I received no errors. Here is all my test code:
create table dbo.CoursePar (
CourseParID int identity(1,1) not null,
CourseID int,
Hole1 smallint,
Hole2 smallint,
Hole3 smallint
)
create table dbo.ScoreCard (
ScoreCardID int identity(1,1) not null,
GolferID int,
CourseID int,
Hole1 smallint,
Hole2 smallint,
Hole3 smallint
)
go
insert into dbo.CoursePar(CourseID, Hole1, Hole2, Hole3)
select 1,3,3,4 union all
select 2,3,2,3
insert into dbo.ScoreCard (GolferID, CourseID, Hole1, Hole2, Hole3)
select 1,1,4,2,4 union all
select 1,2,3,2,3 union all
select 2,1,5,5,6 union all
select 2,2,4,4,3
select
*
from
dbo.CoursePar
select
*
from
dbo.ScoreCard
SELECT
GolferID, t2.CourseID
, t2.Hole1 - t1.Hole1
, t2.Hole2 - t1.Hole2
, t2.Hole3 - t1.Hole3
FROM
dbo.ScoreCard t2
INNER JOIN dbo.CoursePar t1
ON (t1.CourseID=t2.CourseID)
go
drop table dbo.CoursePar
drop table dbo.ScoreCard
April 16, 2008 at 2:12 pm
SELECT
GolferID, t2.CourseID
, t2.Hole1 - t1.Hole1
, t2.Hole2 - t1.Hole2
, t2.Hole3 - t1.Hole3
FROM
dbo.ScoreCard t2
INNER JOIN dbo.Event
ON (t2.EventID = Event.EventID)
INNER JOIN dbo.CoursePar t1
ON (Event.CourseID=t2.CourseID)
Try that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 2:18 pm
...What Gsquared said....:D
Actually...ALMOST what GSquared said....
SELECT
GolferID, t1.CourseID --<--t2 doesn't have courseID, so use either event or t1
, t2.Hole1 - t1.Hole1
, t2.Hole2 - t1.Hole2
, t2.Hole3 - t1.Hole3
FROM
dbo.ScoreCard t2
INNER JOIN dbo.Event
ON (t2.EventID = Event.EventID)
INNER JOIN dbo.CoursePar t1
ON (Event.CourseID=t2.CourseID)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 2:21 pm
Tried it and got Invalid column name 'CourseID'.
Maybe I need to redo my tables. Do you think?
April 16, 2008 at 2:25 pm
Every place you see T2.courseID, replace with T1.courseID (there's a second one in the ON clause)....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 2:32 pm
Matt / GSquared
BINGO. 😀
I used event.courseID instead of t2.courseID as you instructed and also changed (Event.CourseID=t2.CourseID) to Event.CourseId=Event.CourseID)
The results:
The golferId, courseId, Hole1 diff, Hole2 diff, and Hole3 diff.
Perfect. Thanks for all your help..... This site is great.
April 16, 2008 at 2:34 pm
wait a second.... The ON clause must link a field from one table to a field from the OTHER table, not to itself (that wouldn't do anything...).
The ON clause MUST read
event.courseID=T1.courseID
.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 10:22 pm
Matt,
You are correct again. The event.courseID=T1.courseID works like a charm. Thank you again for all your help.
April 16, 2008 at 11:07 pm
Matt Miller (4/16/2008)
Barry has a typo in the ON clause...should be:
ON (t1.CourseID=t2.CourseID)
Thanks for the assist, Matt.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply