如何在多个列上执行内部联接

我正在做一个家庭作业项目，我应该执行一个数据库查询，按城市名称或机场代码查找航班，但flights表只包含机场代码，因此如果我想按城市搜索，我必须加入airports表。

Airports表具有以下列：code, city
FLIGHTS表具有以下列：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')

更新

我还发现SQL JOIN语句的以下直观表示形式非常作为如何构造SQL语句的通用指南很有帮助。

507614 次浏览
小开

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
小开

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
...
小开
最佳答案

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.

小开

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


提交答案