How to do an INNER JOIN on multiple columns

sql database

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.

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

Daniel Vassallo

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.

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

OH, I know why :) it's supposed to be INNER JOIN not INNER_JOIN... DOH!

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 Why you used or not and?

@PHPFan - Because the original query uses OR, not AND.

Asclepius

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

shriek

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

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?

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.

Phil Rykoff

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

Why not just use airports.code instead of fromairports.code and toairports.code? You then won't need as fromairports, airports as toairports.

Robbert

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

Subarnarekha

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

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US