mysql 出现 2 个内部连接路径引用相同结束位置的问题

Question

抱歉标题令人困惑，我是 mysql 新手，在 2 个连接路径(总共 4 个内部连接)方面遇到了麻烦。

两条连接路径都抛出不同的中间表，但引用相同的结束位置。

第一条路径

stockitem.dep_id -> dep.dep_id 和 dep.city_id -> city.city_id

第二条路

stockitem.manu_id ->Manufacturer.manu_id 和Manufacturer.city_id ->city.city_id

最后一列被 city.city_name 引用

这是我为此编写的内容:

SELECT
stockitem.stk_id as 'STOCK ID', 
stockitem.stk_name as 'STOCK NAME', 
city.city_name as 'MANUFACTURER CITY', 
city.city_name as 'DEPOT CITY'
FROM stockitem
JOIN manufacturer
    ON stockitem.manu_id = manufacturer.manu_id
JOIN city
    ON manufacturer.city_id = city.city_id
AND
JOIN depot
    ON stockitem.dep_id = depot.dep_id
JOIN city
    ON depot.city_id = city.city_id;

Answer 1

第一个错误是由于 JOIN 之间放置了 AND 造成的，因此我已将其删除。第二个错误是因为您JOINcity两次(这需要在其中一个连接上使用别名)。

SELECT
stockitem.stk_id as 'STOCK ID', 
stockitem.stk_name as 'STOCK NAME', 
city.city_name as 'MANUFACTURER CITY', 
city.city_name as 'DEPOT CITY'
FROM stockitem
JOIN manufacturer
    ON stockitem.manu_id = manufacturer.manu_id
JOIN city
    ON manufacturer.city_id = city.city_id
JOIN depot
    ON stockitem.dep_id = depot.dep_id;
JOIN city as c2
    ON depot.city_id = c2.city_id