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!

Hint: You need to lookup two cities for each record (one for the fairport and the other for the tairport. It is therefore OK (indeed needed) to have _two_ JOINs, with the airports table, but one of them based on fairport the other on tairport. — mjv, Mar 02 '10 at 21:14
Hint2: you'll therefore need to also alias the airports table so that you know how to differentiate them (i.e. which is the airport table with the fairport lookup and with the tairport lookup). The SQL keyword for alias is AS (alhtough it may be omitted, i.e. ...JOIN airports [AS] FA ON FA.code = flights.tairport ...) — mjv, Mar 02 '10 at 21:18

Daniel Vassallo · Accepted Answer · 2010-03-02T21:39:10.877

165

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.

edited Mar 02 '10 at 21:39

answered Mar 02 '10 at 21:15

Daniel Vassallo

337,827
72
505
443

OK, I tried the above solution and go the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER_JOIN airports to_port ON (to_port.code = flights.tairport) WHERE ' at line 7 – Kiril Mar 02 '10 at 21:26
2

OH, I know why :) it's supposed to be INNER JOIN not INNER_JOIN... DOH! – Kiril Mar 02 '10 at 21:37
32

If airports table is huge, is it better to join it just once on multiple condition. Something like - `flights f INNER JOIN airports a ON a.code = f.fairport OR a.code = f.tairport` Please suggest. – Ankur-m Oct 22 '13 at 06:33
1

@Ankur-m Why you used or not and? – PHPFan Nov 09 '20 at 05:38
1

@PHPFan - Because the original query uses OR, not AND. – Ankur-m Nov 12 '20 at 10:09

score 49 · Answer 2 · edited Oct 19 '20 at 15:10

49

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

edited Oct 19 '20 at 15:10

Asclepius

57,944
17
167
143

answered May 03 '16 at 19:28

ez33

531
4
4

This is actully pretty good I tried it also with `OR` instead of `AND` – Federico Aug 02 '22 at 12:45

score 31 · Answer 3 · edited Aug 02 '21 at 21:36

31

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

edited Aug 02 '21 at 21:36

shriek

5,605
8
46
75

answered Mar 02 '10 at 21:14

Paul Creasey

28,321
10
54
90

1

I have asked it above, thought will ask it here as well - If airports table is huge (AND there is more filter to the whole query using WHERE condition), is it better to join it just once on multiple condition. Something like - `flights f INNER JOIN airports a ON a.code = f.fairport OR a.code = f.tairport` Does it make any difference? What do you think? – Ankur-m Oct 22 '13 at 07:26
1

It makes a difference to the results, the former produces one row per flight with from and to, your suggestion would produce 2 rows per flight, one row with the from and one with the to airport. It would be faster to join only once though. – Paul Creasey Oct 23 '13 at 07:53

score 21 · Answer 4 · answered Mar 02 '10 at 21:18

21

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

answered Mar 02 '10 at 21:18

Phil Rykoff

11,999
3
39
63

Why not just use `airports.code` instead of `fromairports.code` and `toairports.code`? You then won't need `as fromairports, airports as toairports`. – Asclepius Oct 19 '20 at 15:09

score 4 · Answer 5 · edited Jan 05 '14 at 04:47

4

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

edited Jan 05 '14 at 04:47

Robbert

6,481
5
35
61

answered Mar 02 '10 at 21:15

MisterZimbu

2,673
3
27
28

score 3 · Answer 6 · answered Dec 27 '21 at 18:39

3

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

answered Dec 27 '21 at 18:39

Subarnarekha

95
2
6

How to do an INNER JOIN on multiple columns

Update

6 Answers6

Linked