How to join 3 tables in MySQL

Question

I’m looking at 3 tables here with:

1 called Users containing a UserID.
UserID is linked by a foreign key to UserID in a table called Orders.
Within Orders there’s a PassengerID linked by a foreign key to a table called Passengers.

Is there anyway I can link these tables to identify the UserID it’s associated OrderID and that OrderIDs associated passengerID.

Answer 1

This should be straightforward, probably something like this:

SELECT u.UserID, o.OrderID, p.PassengerID
FROM Users u
  JOIN Orders o ON u.UserID=o.UserID
  JOIN Passengers p ON o.PassengerID=p.PassengerID
WHERE ...

The WHERE condition, if you want the WHERE clause at all, could be e.g. u.UserID=5 to retrieve only the results for a certain user, or p.PassengerID=4 for a certain passenger.

A slightly shorter way of writing the same query:

SELECT UserID, o.OrderID, PassengerID
FROM Users u
  JOIN Orders o USING(UserID)
  JOIN Passengers p USING(PassengerID)
WHERE ...

Note that here the aliases u and p are not needed in the SELECT clause. USING can only be used because the columns have identical names in the two tables being joined.