How to do an INNER JOIN on multiple columns

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!

6 Answers

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.

Why can't it just use AND in the ON clause? For example:

SELECT * FROM flights INNER JOIN airports ON ((airports.code = flights.fairport) AND (airports.code = flights.tairport)) 

something like....

SELECT f.* ,a1.city as from ,a2.city as to FROM flights f INNER JOIN airports a1 ON f.fairport = a1.code INNER JOIN airports a2 ON f.tairport = a2.code 

if mysql is okay for you:

SELECT flights.*, fromairports.city as fromCity, toairports.city as toCity FROM flights LEFT JOIN (airports as fromairports, airports as toairports) ON (fromairports.code=flights.fairport AND toairports.code=flights.tairport ) WHERE flights.fairport = '?' OR fromairports.city = '?' 

edit: added example to filter the output for code or city

If you want to search on both FROM and TO airports, you'll want to join on the Airports table twice - then you can use both from and to tables in your results set:

SELECT Flights.*,fromAirports.*,toAirports.* FROM Flights INNER JOIN Airports fromAirports on Flights.fairport = fromAirports.code INNER JOIN Airports toAirports on Flights.tairport = toAirports.code WHERE ... 

SELECT * FROM flights INNER JOIN airports ON ((airports.code = flights.fairport) OR (airports.code = flights.tairport)) 

Can the OR be used inside JOIN Condition as above