Select cities of airports from one table which is related to the other table with 2 columns: departu-CodePudding

I am having trouble with the selection of needed columns by using SQL. I need to find from where and to where a passenger has flown.

I have added a schema diagram and highlighted the needed tables.

My code is below and it needs some help at the last stage:

SELECT f.departure_airport, f.arrival_airport, city
FROM flights f
   JOIN ticket_flights tf on f.flight_id=tf.flight_id
   JOIN tickets t on tf.ticket_no=t.ticket_no
   JOIN airports a on f.departure_airport=a.airport_code OR
 f.arrival_airport=a.airport_code

Now I have only 3 columns: departure_airport, arrival_airport, city.

I need to get a table with 4 columns: departure_airport, arrival_airport, city_departure, city_arrival.

I would really appreciate your help with the final part of SQL query and explanation of such type of relationship between tables Airports and Flights.

CodePudding user response：

Just add another join to the airports table for the departure airport:

SELECT f.departure_airport, f.arrival_airport,  
       a.city as city_arrival,
       d.city as city_departure
FROM flights f 
JOIN ticket_flights tf 
  on f.flight_id=tf.flight_id 
JOIN tickets t 
  on tf.ticket_no=t.ticket_no 
JOIN airports a 
  on f.arrival_airport=a.airport_code 
JOIN airports d 
  on f.departure_airport=d.airport_code 
[...]