t-sql 2012 with implicit join

Question

Post reply

t-sql 2012 with implicit join

wendy elizabeth

SSCoach

Points: 16915
More actions
April 24, 2014 at 3:58 pm

#284819

I am trying to understand how the following t-sql 2012 with an implicit join is currently executed:
SELECT distinct custyear
FROM dbo.Individual INNER JOIN
dbo.Course INNER JOIN
dbo.CalendarYear ON dbo.Course.CalendarYearID = dbo.CalendarYear.CalendarYearID
AND CalendarYear.endYear >= (SELECT DISTINCT A.endYear FROM CalendarYear A WHERE A.active = 1)
INNER JOIN
dbo.Cust ON dbo.CalendarYear.CustID = dbo.Cust.CustID INNER JOIN
dbo.BridgeCust ON dbo.Course.CourseID = dbo.BridgeCust.CourseID ON
dbo.Individual.StudID = dbo.BridgeCust.StudID INNER JOIN
dbo.User INNER JOIN
dbo.BridgeUser ON dbo.User.UserID = dbo.BridgeUser.UserID
ON dbo.BridgeCust.CourseID = dbo.BridgeUser.CourseID
There is no direct join for the dbo.Individual with the dbo.Course table. The first time the dbo.Individual
is being joined is with the BridgeCust table. The bridgeCust table is only setup for key to the of this
table plus the key to the dbo.Individual and the dbo.Course table.
Thus would you explain to me how implicit join is setup?
Would you write the sql to show me the sql is setup to really be executed explicitly?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That code is full of syntax errors. Missing conditions on your JOINs and ON used more than once.

BTW, your subquery might give you an error if more than one year is marked as active.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

If you look carefully in the code you actually will find the join criteria in the original code.

I have reformatted the code and moved things around a bit:

SELECT DISTINCT custyear

FROM

dbo.Individual

INNER JOIN dbo.BridgeCust

ON dbo.Individual.StudID = dbo.BridgeCust.StudID

INNER JOIN dbo.Course

ON dbo.Course.CourseID = dbo.BridgeCust.CourseID

INNER JOIN dbo.CalendarYear

ON

dbo.Course.CalendarYearID = dbo.CalendarYear.CalendarYearID

AND CalendarYear.endYear >= (

SELECT DISTINCT A.endYear

FROM CalendarYear A

WHERE A.active = 1

)

INNER JOIN dbo.Cust

ON dbo.CalendarYear.CustID = dbo.Cust.CustID

INNER JOIN dbo.BridgeUser

ON dbo.BridgeCust.CourseID = dbo.BridgeUser.CourseID

INNER JOIN dbo.User

ON dbo.User.UserID = dbo.BridgeUser.UserID

Of course I couldn't test anything as you didn't provide any DDL for the tables, sample data, and expected results based on the sample data.