SQL Join Problem

I have a number of tables I'm trying to join 2 of which are:
TDrivers
      intDriverID
      strFirstName
      strMiddleName
      strLastName
      strAddress
      strCity
      strState
      strZipCode
      strPhoneNumber

TScheduledRoutes
      intScheduledTimeID
      intRouteID
      intBusID
      intDriverID
      intAlternateDriverID

I am trying to join both TScheduledRoutes.intDriverID and TScheduledRoutes.intAletnetnateDriverID back to TDrivers in order to get the driver information and I'm not able to make it happen. the script for the join is:

SELECT
	 TR.intRouteID
	,TR.strRoute
	,TR.strRouteDescription
	,TST.intScheduledTimeID
	,TST.strScheduledTime
	,TB.intBusID
	,TB.strBus
	,TB.intCapacity
	,TD.intDriverID 
	,TD.strLastName
			+ ', '
			+ TD.strFirstName AS strDriver
	,TD.intDriverID as intAlternateDriverID
	,TD.strLastName
			+ ', '
			+ TD.strFirstName AS strAlternateDriver
FROM
	 TRoutes				AS TR
	,TScheduledRoutes	AS TSR
	,TScheduledTimes		AS TST
	,TBuses				AS TB
	,TDrivers				AS TD
WHERE
	TR.intRouteID				= TSR.intRouteID
AND	TSR.intScheduledTimeID	= TST.intScheduledTimeID
AND TSR.intBusID				= TB.intBusID
AND TSR.intDriverID			= TD.intDriverID
AND	TSR.intAlternateDriverID	= TD.intDriverID

Open in new window

OhioWoodWrightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Well without any knowledge of your tables and data, my crystal ball says stop using the where clause for joins.

It also suggests this for your FROM clause:
FROM TRoutes AS TR
  INNER JOIN TScheduledRoutes AS TSR
        ON TR.intRouteID = TSR.intRouteID
  INNER JOIN TScheduledTimes AS TST
        ON TSR.intScheduledTimeID = TST.intScheduledTimeID
  INNER JOIN TBuses AS TB
        ON TSR.intBusID = TB.intBusID
  INNER JOIN TDrivers AS TD
        ON TSR.intDriverID = TD.intDriverID
        OR TSR.intAlternateDriverID = TD.intDriverID
;

Open in new window

The OR condition might not work, and if that is true then you might need to join the drivers table twice something like this: (and note the LEFT JOIN)
  INNER JOIN TDrivers AS TD
        ON TSR.intDriverID = TD.intDriverID
  LEFT JOIN TDrivers AS TDalt
        OR TSR.intAlternateDriverID = TDalt.intDriverID

Open in new window


Only with access to the tables and some data could I be more definite
0
Ryan ChongCommented:
I would suggest to use Paul's second suggestion.

so a more complete script could be like this:

SELECT
	 TR.intRouteID
	,TR.strRoute
	,TR.strRouteDescription
	,TST.intScheduledTimeID
	,TST.strScheduledTime
	,TB.intBusID
	,TB.strBus
	,TB.intCapacity
	,TSR.intDriverID 
	,TD1.strLastName
			+ ', '
			+ TD1.strFirstName AS strDriver
	,TSR.intAlternateDriverID
	,TD2.strLastName
			+ ', '
			+ TD2.strFirstName AS strAlternateDriver
FROM
	TRoutes AS TR
	INNER JOIN TScheduledRoutes AS TSR ON TR.intRouteID = TSR.intRouteID
	INNER JOIN TScheduledTimes AS TST ON TSR.intScheduledTimeID = TST.intScheduledTimeID
	INNER JOIN TBuses AS TB ON TSR.intBusID = TB.intBusID
	LEFT JOIN TDrivers AS TD1 ON TSR.intDriverID = TD1.intDriverID
	LEFT JOIN TDrivers AS TD2 ON TSR.intAlternateDriverID = TD2.intDriverID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Thanks Ryan, may need to deal with NULLS coming from those left joins but looks good.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.