Как сделать INNER JOIN на нескольких столбцах

Я работаю над домашним проектом, и я должен выполнить запрос к базе данных, который находит полеты либо по названию города, либо по коду аэропорта, но таблица flights содержит только коды аэропорта, поэтому, если я хочу поиск по городу Мне нужно присоединиться к таблице airports.

Таблица аэропортов имеет следующие столбцы: code, city
Таблица рейсов имеет следующие столбцы: airline, flt_no, fairport, tairport, depart, arrive, fare
Столбцами fairport и tairport являются коды аэропорта от и до.
Столбцы depart и arrive - это даты отправления и прибытия.

Я придумал запрос, который сначала соединяет полеты в столбце fairport и столбце airports.code. Для того чтобы я мог соответствовать tairport, мне нужно выполнить другое соединение в предыдущих совпадениях из первого соединения.

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 = '?')

Мой запрос возвращает правильные результаты, и этого будет достаточно для домашней работы, но мне интересно, могу ли я JOIN на нескольких столбцах? Как мне построить предложение WHERE, чтобы оно соответствовало месту отправления и города назначения?

Ниже приведен псевдо-запрос на то, что я хочу добиться, но я не могу правильно получить синтаксис, и я не знаю, как представить таблицу airports для отправлений и пунктов назначения:

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

Я также нашел это визуальное представление операторов SQL Join, чтобы быть very полезным как общее руководство о том, как создавать инструкции SQL!

Ответы

Ответ 1

Вы можете ПРИСОЕДИНИТЬСЯ с одной и той же таблицей несколько раз, предоставив объединенным таблицам alias, как показано в следующем примере:

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='?'

Обратите внимание, что to_port и from_port являются псевдонимами для первой и второй копий таблицы airports.

Ответ 2

что-то вроде....

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

Ответ 3

если mysql подходит для вас:

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: добавлен пример для фильтрации вывода для кода или города

Ответ 4

Можете ли вы просто использовать и в разделе on?

Например, что-то вроде:

SELECT 
   airline, flt_no, fairport, tairport, depart, arrive, fare
FROM 
   flights
INNER JOIN 
   airports from_port ON (from_port.code = flights.fairport)
   and (to_port.code = flights.tairport)

Ответ 5

Если вы хотите выполнить поиск в обоих аэропортах FROM и TO, вам нужно дважды присоединиться к таблице аэропортов, тогда вы можете использовать как таблицы, так и таблицы в своем наборе результатов:

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