Union associations

Question

My table X has an object name and a manufacturer name, but I need to display them as an object identifier and a manufacturer identifier.

I wrote two connections to display them:

In the first, the identifier of the object is declared:

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

The second shows the manufacturer ID:

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

How can I put them in a single request to show an object identifier and a manufacturer identifier on one result screen?

Answer 1

If you want them to be related based on a table X, try the following:

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

Edit:
, ( ), 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

Answer 2

UNION ALL, .

Answer 3

If you do not mind them in separate lines, follow the UNION ALL statement. If you want both to be on the same row, you may need to make two joins to table X.