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.

Answer 1

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.