Lirik Lirik - 7 months ago 9
SQL Question

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!

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.

Comments