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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · 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

[sql] How to do an INNER JOIN on multiple columns

Examples related to sql

Examples related to database