我正在做一个家庭作业项目,我应该执行一个数据库查询,通过城市名称或机场代码查找航班,但该flights表仅包含机场代码,所以如果我想按城市搜索,我必须加入airports餐桌。
flights
airports
机场表具有以下列:code, city 航班表具有以下列:airline, flt_no, fairport, tairport, depart, arrive, fare 列fairport和tairport是 从 机场代码和 到 机场代码。 列depart和arrive是出发和到达日期。
code, city
airline, flt_no, fairport, tairport, depart, arrive, fare
fairport
tairport
depart
arrive
我想出了一个查询,它首先连接fairport列和airports.code列上的航班。为了让我匹配,tairport我必须对第一次加入的先前匹配执行另一次加入。
airports.code
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子句以使其与出发地和目的地城市/代码相匹配?
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 语句的一般指南 非常有帮助!
您可以通过为连接的表指定别名来多次连接同一个表,如下例所示:
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_portandfrom_port是表的第一个和第二个副本的别名airports。
to_port
from_port