SELECT from a table twice in the same query-CodePudding

I have a table per below, which riches with:

SELECT 
    origin_airport_id, destination_airport_id, hour, minute 
FROM 
    flights 
WHERE 
    year = 2021 AND month = 7 AND day = 28;

Then two columns are generated with two below queries respectively (left to right):

SELECT city 
FROM airports 
JOIN flights ON airports.id = flights.origin_airport_id 
WHERE flights.year = 2021 AND flights.month = 7 AND flights.day = 28;

and

SELECT city 
FROM airports 
JOIN flights ON airports.id = flights.destination_airport_id 
WHERE flights.year = 2021 AND flights.month = 7 AND flights.day = 28;

These are the two related table schemas:

How can the two columns (origin_airport_id and destination_airport_id in flights table) be SELECTed from city column of the airport table?

In fact how can we select city column twice in the same query?

This means I want to replace the two first column of the first picture here with city.

CodePudding user response：

You must join flights to 2 copies of airports:

SELECT orig.city AS orig_city,
       dest.city AS dest_city,
       f.hour,
       f.minute
FROM flights AS f
JOIN airports AS orig ON orig.id = f.origin_airport_id 
JOIN airports AS dest ON dest.id = f.origin_destination_id 
WHERE f.year = 2021 AND f.month = 7 AND f.day = 28;