How to Do an Inner Join on Multiple Columns

Question

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!

Accepted Answer

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.

Answer 2

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))

Answer 3

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

Answer 4

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

Answer 5

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
 ...

Answer 6

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

How to Do an Inner Join on Multiple Columns

Update

Must Read

6 Answers

David Miller