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?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 24, 2014 at 4:22 pm

  #1708635

  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

  April 24, 2014 at 4:33 pm

  #1708636

  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.