sql - 组合连接

Question

我的 X 表有设施名称和制造商名称，但我需要将它们显示为设施 ID 和制造商 ID。

我写了两个连接来显示它们:

第一个显示设施 ID:

select T1.facilityID, t2.*
from lkuFacility t1 right join X t2 on t1.facilityName = t2.facility
and t1.siteCode = t2.siteID
order by siteid

第二个显示制造商 ID:

select T1.manufacturerID, t2.*
from lkuManufacturer t1 right join X t2 on t1.manufacturerName = t2.manufacturer
order by manufacturerid

如何将它们放入一个查询中，以在一个结果屏幕上同时显示设施 ID 和制造商 ID？

Answer 1

如果您希望它们基于表 X 进行链接， 尝试这个:

SELECT f.facilityID, m.manufacturerID, X.*
FROM X
LEFT JOIN lkuFacility AS f
ON f.facilityname = X.facility AND f.siteCode = X.siteID
LEFT JOIN lkiManufacturer AS m
ON m.manufacturerName = X.manufacturer

编辑:
如果您想将它们加入相同的结果集但不并排(如上)，您应该能够使用 UNION像这样的运营商:

select T1.facilityID, t2.*
from lkuFacility t1 right join X t2 on t1.facilityName = t2.facility
and t1.siteCode = t2.siteID
UNION ALL
select T1.manufacturerID, t2.*
from lkuManufacturer t1 right join X t2 on t1.manufacturerName = t2.manufacturer
order by manufacturerid