如何在多个列上执行INNER JOIN [英] How to do an INNER JOIN on multiple columns

问题描述

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

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.

flights表有以下列： code，city

航班表有以下列： airline，flt_no， fairport，tairport，departure，arrival，fare

列 fairport 和 tairport 是从和到机场代码。

离开 到达是出发和抵达的日期。

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.

我想出了一个查询，首先加入 fairport 列和 airports.code 列。为了让我匹配 tairport ，我必须对第一次加入之前的匹配执行另一次加入。

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

我的查询返回正确的结果，它足以满足作业的目的，但我想知道是否可以 JOIN 多列？我如何构造 WHERE 子句，使其与出发地和目的地城市/代码匹配？

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?

伪查询我想要的东西，但我不能得到正确的语法，我不知道如何表示 airport 表的离开和目的地：

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

更新

SQL连接语句的可视表示

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!

推荐答案

您可以使用相同的表格JOIN通过为连接的表提供别名，如下例所示： p>

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

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

这篇关于如何在多个列上执行INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！