title: How to do an INNER JOIN on multiple columns
body: <p>I'm working on a homework project and I'm supposed to perform a database query which finds flights either by the city name or the airport code, but the <code>flights</code> table only contains the airport codes so if I want to search by city I have to join on the <code>airports</code> table.</p>
  
  <p>The airports table has the following columns: <code>code, city</code><br>
  The flights table has the following columns: <code>airline, flt_no, fairport, tairport, depart, arrive, fare</code><br>
  The columns <code>fairport</code> and <code>tairport</code> are the <strong>from</strong> and <strong>to</strong> airport codes.<br>
  The columns <code>depart</code> and <code>arrive</code> are dates of departure and arrival.  </p>
  
  <p>I came up with a query which first joins the flights on the <code>fairport</code> column and the <code>airports.code</code> column.  In order for me to match the <code>tairport</code> I have to perform another join on the previous matches from the first join.</p>
  
  <pre><code>SELECT airline, flt_no, fairport, tairport, depart, arrive, fare
      FROM (SELECT * FROM flights
          INNER JOIN airports
          ON flights.fairport = airports.code
          WHERE (airports.code = '?' OR airports.city='?')) AS matches
      INNER JOIN airports
      ON matches.tairport = airports.code
      WHERE (airports.code = '?' OR airports.city = '?')
  </code></pre>
  
  <p>My query returns the proper results and it will suffice for the purpose of the homework, but I'm wondering if I can <code>JOIN</code> on multiple columns? How would I construct the <code>WHERE</code> clause so it matches the departure and the destination city/code?</p>
  
  <p>Below is a "pseudo-query" on what I want to acheive, but I can't get the syntax correctly and i don't know how to represent the <code>airports</code> table for the departures and the destinations:</p>
  
  <pre><code>SELECT * FROM flights
  INNER JOIN airports
  ON flights.fairport = airports.code AND flights.tairport = airports.code
  WHERE (airports.code = 'departureCode' OR airports.city= 'departureCity') 
      AND (airports.code = 'destinationCode' OR airports.city = 'destinationCity')
  </code></pre>
  
  <h2>Update</h2>
  
  <p>I also found <a href="http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins" rel="noreferrer">this visual representation of SQL Join statements</a> to be <strong>very</strong> helpful as a general guide on how to construct SQL statements!</p>