CODEWITHSUNDEEP
sqljoin
Madam Zu Zu
Madam Zu Zu

Reputation: 6605

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 manufacturerid

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

Upvotes: 5

Views: 5864

Answers (3)

Richard Marskell - Drackir
Richard Marskell - Drackir

Reputation: 13292

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

Upvotes: 7

joksnet
joksnet

Reputation: 2325

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

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 0

Related Questions