SQL 如何在多列上执行 INNER JOIN

How to do an INNER JOIN on multiple columns

提问by Kiril

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 flightstable only contains the airport codes so if I want to search by city I have to join on the airportstable.

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

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 fairportand tairportare the fromand toairport codes.
The columns departand arriveare dates of departure and arrival.

机场表具有以下列：code, city
航班表具有以下列：airline, flt_no, fairport, tairport, depart, arrive, fare
列fairport和tairport是从机场和到机场的代码。
列depart和arrive是出发和到达的日期。

I came up with a query which first joins the flights on the fairportcolumn and the airports.codecolumn. In order for me to match the tairportI have to perform another join on the previous matches from the first join.

我想出了一个查询，它首先将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 = '?')

My query returns the proper results and it will suffice for the purpose of the homework, but I'm wondering if I can JOINon multiple columns? How would I construct the WHEREclause so it matches the departure and the destination city/code?

我的查询返回正确的结果，它足以满足家庭作业的目的，但我想知道我是否可以JOIN在多个列上？我将如何构建该WHERE条款以使其与出发地和目的地城市/代码相匹配？

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 airportstable for the departures and the destinations:

以下是我想要实现的“伪查询”，但我无法正确获取语法，也不知道如何表示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

更新

I also found this visual representation of SQL Join statementsto be veryhelpful as a general guide on how to construct SQL statements!

我也发现了SQL的语句加入这个可视化表示是非常的了解如何构建SQL语句的一般指导帮助！

采纳答案by Daniel Vassallo

You can JOIN with the same table more than once by giving the joined tables an alias, as in the following example:

您可以通过为连接的表指定别名来多次 JOIN 同一个表，如下例所示：

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_portand from_portare aliases for the first and second copies of the airportstable.

请注意，to_port和from_port是airports表的第一个和第二个副本的别名。

回答by Paul Creasey

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

回答by Phil Rykoff

if mysql is okay for you:

如果 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: added example to filter the output for code or city

编辑：添加示例以过滤代码或城市的输出

回答by ez33

Can you just use and in the on clause?

你可以在 on 子句中使用 and 吗？

For example, something like:

例如，类似于：

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)

回答by MisterZimbu

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:

如果您想在 FROM 和 TO 机场上搜索，您需要在 Airports 表上加入两次 - 然后您可以在结果集中使用 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
 ...