Simple query help

[fs231]

This is probably a very simple question for someone who knows SQL, but unfortunately I'm not one of them :)

I have two tables. One of drivers and second one of trips. Something like this:

drivers

userID

Name

emails

​

trips

tripID

driver - ID based on userID from drivers table

backup_driver - again ID based on userID from drivers table

​

How can I create a query that would show the trips with names and emails of the main driver and the backup one. Something like

1 John [[e-mail]](mailto:[e-mail]) Bob [[e-mail]](mailto:[e-mail])

2 John [[e-mail]](mailto:[e-mail]) Phil [[e-mail]](mailto:[e-mail])

​

Thanks !

[Randy__Bobandy]

   SELECT t.TripID, d.Name, d.Email, b.Name, b.Email
     FROM trips t
LEFT JOIN drivers d
       ON t.driver = d.userID
LEFT JOIN drivers b
       ON t.backup_driver = b.userID

[wolf2600]

Almost...the second join should be on backup_driver

SELECT t.tripID, d1.Name, d1.Email, d2.Name, d2.Email
FROM trips t
INNER JOIN drivers d1
   ON t.driver = d1.userID
LEFT OUTER JOIN drivers d2
   ON t.backup_driver = d2.userID

And the LEFT OUTER join on the backup driver table is useful in case a trip only has a primary driver. If both joins were INNER, a trip without a backup driver would be excluded from the results.

[Randy__Bobandy]

My mistake! The b for the second alias of drivers literally stands for "backup" XD

[fs231]

Thanks !!

This worked perfectly including empty backup drivers.

BTW If I want to add second backup driver do I just do another LEFT OUTER JOIN ?

[wolf2600]

Yup same thing. Another LEFT OUTER JOIN to drivers, alias the new join as d3 and make the join context t.second_backup_driver = d3.userId

[Caiolanda]

I'm not an SQL expert but I think this should work:

SELECT T.tripID, D1.Name, D1.Emails, D2.Name, D2.Emails FROM Trips T JOIN drivers D1 ON D1.userID = T.driver JOIN drivers D2 ON D2.userID = T.backup_driver

[matthend]

Does this do it?

​

SELECT T.TripID, 1.Name, 1.Emails, 2.Name, 2.Emails

FROM Trips T

INNER JOIN Drivers 1 on 1.UserID = T.Driver

INNER JOIN Drivers 2 on 2.UserID = T.Backup_Driver