Book

« Conditions Grouping »

Data from Multiple Tables

Let’s try to get a list of seats on an aircraft. Let’s take a query that returns data about an aircraft Airbus A321-200

SELECT *
FROM bookings.aircrafts
WHERE aircrafts.model = 'Airbus A321-200'

According to the description, information about seats on an aircraft is contained in the seats table. Let’s add this table to the list after FROM:

SELECT *
FROM bookings.aircrafts, bookings.seats
WHERE aircrafts.model = 'Airbus A321-200'

We get something different than we wanted. The result shows all seats on all aircraft. In the seats table there is a field containing the aircraft code to which the seat belongs: aircraft_code. The same field exists in the aircraft table. Let’s specify that from the previous selection we only need rows in which the aircraft code from the seats table matches the aircraft code of the aircrafts table:

SELECT *
FROM
  bookings.aircrafts,
  bookings.seats
WHERE
  (aircrafts.model = 'Airbus A321-200')
  AND (seats.aircraft_code = aircrafts.aircraft_code)

There we go! That’s what we wanted!

It remains only to clarify which fields we need to output in the result:

SELECT
  aircrafts.model,
  seats.seat_no
FROM
  bookings.aircrafts,
  bookings.seats
WHERE
  (aircrafts.model = 'Airbus A321-200')
  AND (seats.aircraft_code = aircrafts.aircraft_code)

Now imagine that we need to select data not from two, but from about a dozen tables. In such a case, the WHERE condition will turn into an absolute mess, which is very difficult to understand. Let’s look at another way to write the above query:

SELECT
  aircrafts.model,
  seats.seat_no
FROM
  bookings.aircrafts
  JOIN bookings.seats
    ON seats.aircraft_code = aircrafts.aircraft_code
WHERE
  aircrafts.model = 'Airbus A321-200'

We wrote that we need to select data from the aircrafts table, and to it join (JOIN) data from the seats table so that the aircraft code from the seats table matches the aircraft code of the aircrafts table. The result will be the same, but the query became clearer.

Let’s see what flights our Airbus A321-200 is sent to

SELECT *
FROM
  bookings.flights
  JOIN bookings.aircrafts
    ON aircrafts.aircraft_code = flights.aircraft_code
WHERE
  aircrafts.model = 'Airbus A321-200'

or, specifying only the necessary fields:

SELECT
  aircrafts.model,
  flights.flight_no,
  flights.scheduled_departure
FROM
  bookings.flights
  JOIN bookings.aircrafts
    ON aircrafts.aircraft_code = flights.aircraft_code
WHERE
  aircrafts.model = 'Airbus A321-200'  

Now let’s look at which cities the Airbus A321-200 flies between. Let’s add the departure airport. In the airports table there are city names and airport codes. The departure airport code in the flights table - departure_airport:

SELECT
	aircrafts.model,
	flights.flight_no,
	flights.scheduled_departure,
	airports.*
FROM
	bookings.flights
	JOIN bookings.aircrafts
		ON aircrafts.aircraft_code = flights.aircraft_code
	JOIN bookings.airports
		ON airports.airport_code = flights.departure_airport
WHERE
	aircrafts.model = 'Airbus A321-200'

Let’s leave only the name of the departure city:

SELECT
	aircrafts.model,
	flights.flight_no,
	flights.scheduled_departure,
	airports.city
FROM
	bookings.flights
	JOIN bookings.aircrafts
		ON aircrafts.aircraft_code = flights.aircraft_code
	JOIN bookings.airports
		ON airports.airport_code = flights.departure_airport
WHERE
	aircrafts.model = 'Airbus A321-200'

We also need to add the destination airport. The airport code is in the arrival_airport field of the flights table. We need to link this field to the airports table. But the departure and arrival cities are different. How do we output the airports.city for the departure and arrival cities for one row of the flights table? We have to join the airports table again, but now by linking it with the arrival_airport field:

SELECT
	aircrafts.model,
	flights.flight_no,
	flights.scheduled_departure,
	airports.city
FROM
	bookings.flights
	JOIN bookings.aircrafts
		ON aircrafts.aircraft_code = flights.aircraft_code
	JOIN bookings.airports
		ON airports.airport_code = flights.departure_airport
	JOIN bookings.airports
		ON airports.airport_code = flights.arrival_airport
WHERE
	aircrafts.model = 'Airbus A321-200'

In this form, the query will complain that the airports table is specified more than once. And this is where aliases come in handy. Let’s call one airports table dep_arp, and the other arr_arp shortened from “departure airport” and “arrival airport” respectively:

SELECT
	aircrafts.model,
	flights.flight_no,
	flights.scheduled_departure,
	dep_arp.city "Departure City",
	arr_arp.city "Arrival City"
FROM
	bookings.flights
	JOIN bookings.aircrafts
		ON aircrafts.aircraft_code = flights.aircraft_code
	JOIN bookings.airports dep_arp
		ON dep_arp.airport_code = flights.departure_airport
	JOIN bookings.airports arr_arp
		ON arr_arp.airport_code = flights.arrival_airport
WHERE
	aircrafts.model = 'Airbus A321-200'

That’s it. No more confusion with airports.

« Conditions Grouping »