在同一个表中进行两次内部联接

Question

我的内部联接有问题，这些是我的带有FK和PK 的表

TABLE CITY
city_id (PK)
city_name
state
TABLE DEPOT
dep_id (PK)
capacity
city_id (FK) references CiTy
TABLE MANUFACTURER
manu_id (PK)
manu_name
city_id (FK) references city

所以我只想让结果看起来像这样：

DEPOT_CITY_name(referencese from city_id), MANUFACTURER_CITY_name(references from city_id)

感谢

Answer 1

在from子句中用不同的别名列出两次表。

这将为您提供不同的城市，一个为制造商，另一个为仓库。

SELECT dc.city_name AS depot_city
,      mc.city_name AS manufacturer_city
FROM   DEPOT AS d
JOIN   CITY AS dc
    ON dc.city_id = d.city_id
JOIN   MANUFACTURER AS m
    ON m.some_column = d.some_column -- or however these tables relate
JOIN   CITY AS mc
    ON mc.city_id = m.city_id

Answer 2

只需在cityid:上执行两个左联接

select depot.city_id, Manufacturer.city_id from
depot  left join city on city.city_id = depot.city_id
       left join Manufacturer on Manufacturer.city_id = city.city_id