Combining Joins

My X table has the facility name, and manufacturer name, but I need to display them as facilityID and manufacturerID.

I wrote two joins to display them:

The first one dislays facility 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

The second one displays manufacturer ID:

select T1.manufacturerID, t2.*
from lkuManufacturer t1 right join X t2 on t1.manufacturerName = t2.manufacturer
order by manufacturer开发者_JAVA百科id

How can I put them into one query, to show both facility ID and manufacturer ID on one results screen?

---

If you want them linked based on table X, try this:

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:
If you want to join them into the same result set but not side-by-side (as above), you should be able to use the UNION operator like so:

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

---

If you don't mind them on separate lines then do a UNION ALL statement. If you want both on the same line you may have to do two joins to the X table.

---

You could use the UNION ALL operator to concatenate the two queries.