如何在 MySQL 的同一列上使用两次 Left Join?

发布于 2024-07-23 19:17:59 字数 920 浏览 8 评论 0原文

我有两张桌子,一张用于机场,一张用于航线。

机场看起来有点像这样

Airports
-------------------------------------
id | code | name                    |
-------------------------------------
01 | LGW  | London Gatwick          |
-------------------------------------
02 | LHR  | London Gatwick          |

等等...

以及另一个类似这样的路线

Routes
---------------------------
id | ORIGIN | DESTINATION |
---------------------------
01 | LGW    | VCE         |
---------------------------
02 | TSF    | LHR         |

等等...

我需要从表中选择路线,但我也想获取机场名称。 令人困惑的是我需要查询机场代码两次。 我正在尝试这样的事情

SELECT routes.*, airports.name as origin_name FROM routes
LEFT JOIN airports ON airports.IATA = routes.origin
LEFT JOIN airports ON airports.IATA = routes.destination
WHERE origin = 'LHR' AND destination = 'VCE' OR origin = 'VCE'

,你可能知道也可能不知道,但行不通。 我该怎么做呢?

I have two tables, one for airports and one for routes.

Airports looks a little like this

Airports
-------------------------------------
id | code | name                    |
-------------------------------------
01 | LGW  | London Gatwick          |
-------------------------------------
02 | LHR  | London Gatwick          |

and so on....

and another for routes like this

Routes
---------------------------
id | ORIGIN | DESTINATION |
---------------------------
01 | LGW    | VCE         |
---------------------------
02 | TSF    | LHR         |

and so on...

I need to Select routes from the table, but I also want to get the airport names as well. The confusing bit is that I need to query the airport code twice. I'm trying something like this

SELECT routes.*, airports.name as origin_name FROM routes
LEFT JOIN airports ON airports.IATA = routes.origin
LEFT JOIN airports ON airports.IATA = routes.destination
WHERE origin = 'LHR' AND destination = 'VCE' OR origin = 'VCE'

Which you may or may not know, doesn't work. How would I go about doing this?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

浅语花开 2024-07-30 19:17:59

使用别名:

SELECT
    routes.*,
    a1.name AS origin_name,
    a2.name AS destination_name
FROM routes r
LEFT JOIN airports a1 ON a1.IATA = r.origin
LEFT JOIN airports a2 ON a1.IATA = r.destination
WHERE
    r.origin = 'LHR' AND r.destination = 'VCE' OR r.origin = 'VCE'

Use aliases:

SELECT
    routes.*,
    a1.name AS origin_name,
    a2.name AS destination_name
FROM routes r
LEFT JOIN airports a1 ON a1.IATA = r.origin
LEFT JOIN airports a2 ON a1.IATA = r.destination
WHERE
    r.origin = 'LHR' AND r.destination = 'VCE' OR r.origin = 'VCE'
以歌曲疗慰 2024-07-30 19:17:59

只需给表指定两个不同的别名即可。 类似的东西(未经测试);

SELECT routes.*, o.name as origin, d.name as destination FROM routes
LEFT JOIN airports o ON o.IATA = routes.origin
LEFT JOIN airports d ON d.IATA = routes.destination
WHERE origin = 'LHR' AND destination = 'VCE' OR origin = 'VCE'

Just give the table two different aliases. Something like (untested);

SELECT routes.*, o.name as origin, d.name as destination FROM routes
LEFT JOIN airports o ON o.IATA = routes.origin
LEFT JOIN airports d ON d.IATA = routes.destination
WHERE origin = 'LHR' AND destination = 'VCE' OR origin = 'VCE'
来日方长 2024-07-30 19:17:59

您需要使用别名添加 AIRPORTS 表两次......

SELECT ORIGIN_AIRPORT.NAME,
       DESTINATION_AIRPORT.NAME
FROM   AIRPORTS ORIGIN_AIRPORT,
       AIRPORTS DESTINATION_AIRPORT,
       ROUTES
WHERE  ROUTES.ORIGIN = ORIGIN_AIRPORT.CODE
AND    ROUTES.DESTINATION = DESTINATION_AIRPORT.CODE;

You need to add the AIRPORTS table twice using aliases....

SELECT ORIGIN_AIRPORT.NAME,
       DESTINATION_AIRPORT.NAME
FROM   AIRPORTS ORIGIN_AIRPORT,
       AIRPORTS DESTINATION_AIRPORT,
       ROUTES
WHERE  ROUTES.ORIGIN = ORIGIN_AIRPORT.CODE
AND    ROUTES.DESTINATION = DESTINATION_AIRPORT.CODE;
榆西 2024-07-30 19:17:59

在表名上添加别名:

SELECT routes.*, a1.name as origin_name FROM routes
LEFT JOIN airports AS a1 ON a1.IATA = routes.origin
LEFT JOIN airports AS a2 ON a2.IATA = routes.destination
WHERE origin = 'LHR' AND destination = 'VCE' OR origin = 'VCE'

Put an alias on the table names:

SELECT routes.*, a1.name as origin_name FROM routes
LEFT JOIN airports AS a1 ON a1.IATA = routes.origin
LEFT JOIN airports AS a2 ON a2.IATA = routes.destination
WHERE origin = 'LHR' AND destination = 'VCE' OR origin = 'VCE'
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文