How to Union 2 tables within a Select w/joins

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

SQL Server 2008 Forums

Transact-SQL (2008)

How to Union 2 tables within a Select w/joins

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-09-16 : 14:37:51

I'm trying to union 2 tables and have those results be in a join within a select that has multiple joins.

Here is what I have but cannot get the correct syntax:


(Select p.ID, p.SSN, p.Gender, p.Birthdate, p.DateOfChange,
pa.AnticpYear, pd.ethnicity,
AH.AttendDate, AH.EStatus
	   	   
from Person AS p
left join PersonAnticipated AS pa
ON p.ID = pa.ID
left join PersonDemographics AS pd
ON p.ID = pd.ID

where p.DateOfChange >= dateadd(day,datediff(day,0,getdate())-1,0))

		
(select ID, AttendDate, EStatus 
from AttendHistory 
where EStatus = 'G49'
union all
select ID, AttendDate, EStatus 
from ExtAttendHistory 
where EStatus = 'G49') AH

Don't know where to put the UNION at in the above select to where I can get the AttendDate & EStatus. I want NULLS returned if no rows are found from joining the unioned tables to the Person table via the ID.

I'd prefer to do this all in one statemente if possible. Can this be done all in one statement or do I need to create a #Temp table for the union & then just left join to it?

Thanks.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 14:46:36

Hello,

Perhaps something in the form of the following;

SELECT <fields>
FROM
(
	<queries with joins> --these will be the data which should return regardless of matches in other tables
) d1
LEFT JOIN
(
	<queries with unions> -- data which may not be present in first table(s)
) d2 ON d2.ID = d1.ID

EDIT. comments added.

This form is known as 'derived tables'.

HTH.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 15:01:32

So your queries might look like the following;

SELECT
	 p.ID, p.SSN, p.Gender, p.Birthdate, p.DateOfChange, p.AnticpYear, p.ethnicity, AH.AttendDate, AH.EStatus
FROM
(
	SELECT p.ID, p.SSN, p.Gender, p.Birthdate, p.DateOfChange, pa.AnticpYear, pd.ethnicity  	   	   
	FROM Person AS p
		LEFT JOIN PersonAnticipated AS pa ON p.ID = pa.ID
		LEFT JOIN PersonDemographics AS pd ON p.ID = pd.ID
	WHERE p.DateOfChange >= dateadd(day,datediff(day,0,getdate())-1,0)
) p

LEFT JOIN

(		
	SELECT ID, AttendDate, EStatus 
	FROM AttendHistory 
	WHERE EStatus = 'G49'

	UNION ALL

	SELECT ID, AttendDate, EStatus 
	FROM ExtAttendHistory 
	WHERE EStatus = 'G49'

) AH ON AH.ID = p.ID

HTH.

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-09-16 : 15:07:49

quote:
Originally posted by ehorn

Hello,

Perhaps something in the form of the following;

SELECT <fields>
FROM
(
<queries with joins> --these will be the data which should return regardless of matches in other tables
) d1
LEFT JOIN
(
<queries with unions> -- data which may not be present in first table(s)
) d2 ON d2.ID = d1.ID

EDIT. comments added.

This form is known as 'derived tables'.

HTH.

Thanks for the suggestion & what I am trying to achieve is called. Knowing that I can easly google 'derived tables' for more help.

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-09-16 : 15:09:54

quote:

Originally posted by ehorn

So your queries might look like the following;

SELECT
	 p.ID, p.SSN, p.Gender, p.Birthdate, p.DateOfChange, p.AnticpYear, p.ethnicity, AH.AttendDate, AH.EStatus
FROM
(
	SELECT p.ID, p.SSN, p.Gender, p.Birthdate, p.DateOfChange, pa.AnticpYear, pd.ethnicity  	   	   
	FROM Person AS p
		LEFT JOIN PersonAnticipated AS pa ON p.ID = pa.ID
		LEFT JOIN PersonDemographics AS pd ON p.ID = pd.ID
	WHERE p.DateOfChange >= dateadd(day,datediff(day,0,getdate())-1,0)
) p

LEFT JOIN

(		
	SELECT ID, AttendDate, EStatus 
	FROM AttendHistory 
	WHERE EStatus = 'G49'

	UNION ALL

	SELECT ID, AttendDate, EStatus 
	FROM ExtAttendHistory 
	WHERE EStatus = 'G49'

) AH ON AH.ID = p.ID

HTH.

This looks like what I need. I'll give it a try & post back.
Thanks again.

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-09-16 : 15:44:42

Thanks HTH the above code works.

It gives me more than one row per ID but I may just need to add a ROW_NUMBER().

Anyway you pointed me in the right directions.

Thanks again & have a good weekend!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 15:47:39

quote:
Originally posted by Sqlraider

Thanks HTH the above code works.

It gives me more than one row per ID but I may just need to add a ROW_NUMBER().

Anyway you pointed me in the right directions.

Thanks again & have a good weekend!

yvw, and have a nice weekend yourself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources