How to do an INNER JOIN on multiple columns

database, sql

Solution

You can JOIN with the same table more than once by giving the joined tables an alias, as in the following example:

SELECT 
    airline, flt_no, fairport, tairport, depart, arrive, fare
FROM 
    flights
INNER JOIN 
    airports from_port ON (from_port.code = flights.fairport)
INNER JOIN
    airports to_port ON (to_port.code = flights.tairport)
WHERE 
    from_port.code = '?' OR to_port.code = '?' OR airports.city='?'

Note that the `to_port` and `from_port` are aliases for the first and second copies of the `airports` table.

Problem

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 `flights` table only contains the airport codes so if I want to search by city I have to join on the `airports` table. The airports table has the following columns: `code, city` The flights table has the following columns: `airline, flt_no, fairport, tairport, depart, arrive, fare` The columns `fairport` and `tairport` are the from and to airport codes. The columns `depart` and `arrive` are dates of departure and arrival. I came up with a query which first joins the flights on the `fairport` column and the `airports.code` column. In order for me to match the `tairport` I have to perform another join on the previous matches from the first join. ``` 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 = '?') ``` My query returns the proper results and it will suffice for the purpose of the homework, but I'm wondering if I can `JOIN` on multiple columns? How would I construct the `WHERE` clause so it matches the departure and the destination city/code? 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 `airports` table for the departures and the destinations: ``` 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') ``` Update I also found this visual representation of SQL Join statements to be very helpful as a general guide on how to construct SQL statements!

Original source