Joining two union statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining two union statements

Author

Topic

mellestad
Starting Member

3 Posts

Posted - 2008-01-18 : 13:32:57

Hello,

I have two union statements that I need to join to each other. Then I need to join that result to another table. So-

[(TBL-A UNION TBL-B)
JOINED TO
(TBL-C UNION TBL-D)]
JOINED TO
TBL-E

Is this a subquery thing, or something else? I have not been able to figure out how to make it work! The unions I have made work fine though, on their own.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-18 : 14:02:46

Here is an example of how to make it work:

declare @a table ( i int, fname char(15) )
declare @b table ( i int, fname char(15) )
declare @c table ( i int, lname char(15) )
declare @d table ( i int, lname char(15) )

insert @a ( i, fname )
select 0, 'Carl' union
select 1, 'Susan'

insert @b ( i, fname )
select 2, 'Joey'

insert @c ( i, lname )
select 0, 'Montgomery'

insert @d ( i, lname )
select 1, 'Jacobs' union
select 2, 'Peterson'

select fnames.i, fnames.fname, lnames.lname
from (
  select i, fname from @a union
  select i, fname from @b ) fnames
join (
  select i, lname from @c union
  select i, lname from @d ) lnames on fnames.i = lnames.i

mellestad
Starting Member

3 Posts

Posted - 2008-01-18 : 14:43:55

Awesome, thank you! So, if I want to join another table to the results I just got, do I just put another join statement before those two, and enclose that whole thing? Here is what I have so far:

select line.sopnumbe, line.itemnmbr, line.qtyorder, line.reqshipdate, header.custnmbr
from (
select sopnumbe, custnmbr FROM sop10100
UNION ALL
select sopnumbe, custnmbr FROM sop30200) header
join (
select sopnumbe, itemnmbr, qtyorder, reqshipdate FROM sop10200
UNION ALL
select sopnumbe, itemnmbr, qtyorder, reqshipdate FROM sop30300) line on header.sopnumbe = line.sopnumbe

mellestad
Starting Member

3 Posts

Posted - 2008-01-18 : 15:38:15

Nevermind, I think I figured it out. I just needed to add a left join to the existing statements, and tie it to both the others. Thanks again!
Final code:

SELECT line.SOPNUMBE, line.ITEMNMBR, line.QTYORDER, line.ReqShipDate, header.CUSTNMBR, dbo.SOP60300.CUSTITEMNMBR
FROM
(SELECT SOPNUMBE, CUSTNMBR
FROM dbo.SOP10100
UNION ALL
SELECT SOPNUMBE, CUSTNMBR
FROM dbo.SOP30200) AS header
INNER JOIN
(SELECT SOPNUMBE, ITEMNMBR, QTYORDER, ReqShipDate
FROM dbo.SOP10200
UNION ALL
SELECT SOPNUMBE, ITEMNMBR, QTYORDER, ReqShipDate
FROM dbo.SOP30300) AS line ON header.SOPNUMBE = line.SOPNUMBE
LEFT OUTER JOIN
dbo.SOP60300 ON line.ITEMNMBR = dbo.SOP60300.ITEMNMBR AND header.CUSTNMBR = dbo.SOP60300.CUSTNMBR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources