How do I use a Left Join twice on the same colum with MySQL?

Question

I have two tables, one for airports and one for routes.

Airports looks a little like this

Airports
-------------------------------------
id | code | name                    |
-------------------------------------
01 | LGW  | London Gatwick          |
-------------------------------------
02 | LHR  | London Gatwick          |

and so on....

and another for routes like this

Routes
---------------------------
id | ORIGIN | DESTINATION |
---------------------------
01 | LGW    | VCE         |
---------------------------
02 | TSF    | LHR         |

and so on...

I need to Select routes from the table, but I also want to get the airport names as well. The confusing bit is that I need to query the airport code twice. I'm trying something like this

SELECT routes.*, airports.name as origin_name FROM routes
LEFT JOIN airports ON airports.IATA = routes.origin
LEFT JOIN airports ON airports.IATA = routes.destination
WHERE origin = 'LHR' AND destination = 'VCE' OR origin = 'VCE'

Which you may or may not know, doesn't work. How would I go about doing this?

Answer 1

Use aliases:

SELECT
    routes.*,
    a1.name AS origin_name,
    a2.name AS destination_name
FROM routes r
LEFT JOIN airports a1 ON a1.IATA = r.origin
LEFT JOIN airports a2 ON a1.IATA = r.destination
WHERE
    r.origin = 'LHR' AND r.destination = 'VCE' OR r.origin = 'VCE'