SQL: 2 joins to the same table, with the same key

I need advice from someone who really knows SQL.

This SQL command is simple and works, and hopefully the intention is clear. I get the actual contact name by joining on the contact id:

SELECT deliveries.departure_contact_id, contacts.name_last FROM deliveries
  LEFT OUTER JOIN contacts ON (deliveries.departure_contact_id = contacts.contacts_id)

However, what happens if I have two contact_id columns, and I want to get the full names for both? This is ambiguous, and incorrect:

SELECT deliveries.departure_contact_id, contacts.name_last, deliveries.arrival_contact_id, contacts.name_last FROM deliveries
  LEFT OUTER JOIN contacts ON (deliveries.departure_contact_id = contacts.contacts_id)
  LEFT OUTER JOIN contacts ON (deliveries.arrival_contact_id = contacts.contact_id)

Update: Thanks for all the comments. This seems to be common knowledge. You can use AS with LEFT OUTER JOIN to
give the join an alias, then use that alias name instead of the table name (even in the ON clause of the JOIN), like so:

SELECT deliveries.departure_contact_id, departure_contacts.name_last, deliveries.arrival_contact_id, arrival_contacts.name_last
FROM deliveres
  LEFT OUTER JOIN contacts AS departure_contacts ON (deliveries.departure_contact_id = departure_contacts.contacts_id)
  LEFT OUTER JOIN contacts AS arrival_contacts ON (deliveries.arrival_contact_id = arrival_contacts.contact_id)

That's a simple change in a generic function in Glom, and I can use the relationship names for the aliases.

Leave a Reply

Your email address will not be published. Required fields are marked *