title: How do I use a Left Join twice on the same colum with MySQL?
body: <p>I have two tables, one for airports and one for routes.</p>
  
  <p>Airports looks a little like this</p>
  
  <pre><code>Airports
  -------------------------------------
  id | code | name                    |
  -------------------------------------
  01 | LGW  | London Gatwick          |
  -------------------------------------
  02 | LHR  | London Gatwick          |
  </code></pre>
  
  <p>and so on....</p>
  
  <p>and another for routes like this</p>
  
  <pre><code>Routes
  ---------------------------
  id | ORIGIN | DESTINATION |
  ---------------------------
  01 | LGW    | VCE         |
  ---------------------------
  02 | TSF    | LHR         |
  </code></pre>
  
  <p>and so on...</p>
  
  <p>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</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>Which you may or may not know, doesn't work. How would I go about doing this?</p>