Joining 2 result sets - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Joining 2 result sets

Author

Topic

roger00713
Starting Member

11 Posts

Posted - 2011-06-02 : 23:42:13

Hi.
I have been having a little trouble getting the results to come out the way i need it to.
What im trying to achieve is to display the number of students that live in a certain postcode for junior and senior students.
All student records are in the same table so what im doing is creating 2 separate queries to group the data for junior and Senior. As i need this data to be displayed in a certain way in a report i have then tried to use a join between these queries.
I need the results to come out with 3 columns. Postcode, Junior Students and Senior Students.
My issue is that there are 20 different postcodes in senior and only 15 in junior therefor when i try to join this together based on the postcode field im only receiving the postcodes that are in both result sets. Or if i use a right join i get all postcodes but where a postcode is only in one result set it appears as null.
Does anyone have any ideas on another way to get this to display how i need it to. All help will be much appreciated.
Here is an example of my code:

select junior.Postcode, isnull(junior.Junior,0) as Junior, ISNULL(secondary.Secondary,0) as Secondary
from

(
select um.postcode as postcode, count(st.stkey) as Secondary

from St left join UM on ST.HOMEKEY = UM.umkey

where school_year in ('07','08','09','10','11','12') and status in ('full','part')

group by postcode
)
As Secondary
left join
(
select um.postcode as postcode, count(st.stkey) as Junior

from St left join UM on ST.HOMEKEY = UM.umkey

where school_year in ('00','01','02','03','04','05','06') and status in ('full','part')

group by postcode
)
As junior

on Secondary.postcode = junior.postcode

order by POSTCODE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-03 : 01:18:31

[code]
SELECT UM.postcode,
COUNT(case when school_year in ('00', '01', '02', '03', '04', '05', '06') then 1 end) as Junior,
COUNT(case when school_year in ('07', '08', '09', '10', '11', '12') then 1 end) as Seconday
FROM ST LEFT JOIN UM ON ST.HOMEKEY = UM.UMKEY
WHERE status in ('full', 'part')
GROUP BY UM.postcode
[/code]

KH
[spoiler]Time is always against us[/spoiler]

roger00713
Starting Member

11 Posts

Posted - 2011-06-03 : 02:06:17

Thanks for the response. I have tested this and it appears to be working well.
I didn't realise i was able to use Case whilst using count.
Thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-03 : 02:27:54

Since you are using SQL 2008, you can also use the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources