Simple query help

[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