MySQL innerjoin 3 tables

Question

Supposing I had 3 tables

• Passenger (Id [pk], name)
• Airplane (registration [pk], num_seats)
• Booking (Id, passenger_id [fk -> Passenger.Id], airplane_registration [fk -> Airplane.registration])

The booking table would be a junction object here as there is a many to many relationship between tables Passenger and Booking.

How could I select all the details related to a particular booking id (say 'abcde')?

Is this correct:

SELECT Passenger.name, Airplane.num_seats, Booking.Id
  FROM Booking 
  JOIN Passenger ON Passenger.Id = Booking.passenger_Id
  JOIN Airplane ON Booking.airplane_registration = Airplane.registration
 WHERE Booking.Id = 'abcde';

Is this the right way to do it? Also, If I wanted to select all bookings and their details, would I do it the same way? (Without the where clause)?

I was looking to find out if this was correct for a test as MySQL has gone down on my machine.

Solution

Yes, your query would work for getting the details of the passengers and the flight for the particular booking ID. For getting all bookings, I would add an ORDER BY bookingID and if needed by passenger name and flight registration.