mysql - 显示来自两个外键的列数据

Question

我被卡住了，想知道如何根据两个不同的外键显示具有两列（城市名称）的表。在这种情况下，我想显示制造商城市和仓库城市，而它们都需要访问 CityID。

到目前为止，这是我得出的结论：

SELECT s.StkName, c.CityName AS "Manufacturer City", c.CityName AS 
"Warehouse City", s.SellingPrice FROM
STOCKITEM7364 s
LEFT OUTER JOIN MANUFACTURER7364 m ON m.ManID = s.ManID
LEFT OUTER JOIN CITY7364 c ON c.CityID = m.CityID
LEFT OUTER JOIN WAREHOUSE7364 w ON w.WHID = s.WHID AND w.CityID = c.CityID
WHERE s.StkName LIKE '%n' OR s.StkName LIKE '%e' OR s.StkName LIKE '%o' OR 
s.StkName LIKE '%p' OR s.StkName LIKE '%s';

我真的很感谢有关 sql 的任何帮助或解释

标签： mysqlsqldatabaseoracle

Answer 1

您需要加入两次城市名称表，一次用于制造商，一次用于仓库。试试这个：

SELECT s.StkName, cm.CityName AS "Manufacturer City", cw.CityName AS 
"Warehouse City", s.SellingPrice FROM
STOCKITEM7364 s
LEFT JOIN MANUFACTURER7364 m ON m.ManID = s.ManID
LEFT JOIN CITY7364 cm ON c.CityID = m.CityID
LEFT JOIN WAREHOUSE7364 w ON w.WHID = s.WHID
LEFT JOIN CITY7364 cw ON c.CityID = w.CityID
WHERE s.StkName LIKE '%n' OR s.StkName LIKE '%e' OR s.StkName LIKE '%o' OR 
s.StkName LIKE '%p' OR s.StkName LIKE '%s';

请注意，OUTER在 中不需要LEFT JOIN。