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!

2

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.

5

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

0

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

2

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

1

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy