asked on

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

PortletPaul

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

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

Thanks Ryan, may need to deal with NULLS coming from those left joins but looks good.