Joining two selects - 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 2005 Forums

Transact-SQL (2005)

Joining two selects

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2008-08-14 : 07:07:01

I have two queries producing these results:


Class  Name  Completed Count
---------------------------
9      Name1  1
9      Name2  1
9      Name3  1

Class  Name  Not Completed Count
---------------------------
9      Name1  1
9      Name4  1


How can I join them into this:

Class  Name  Completed Count  Not Completed Count
-------------------------------------
9      Name1  1               1
9      Name2  1               0
9      Name3  1               0
9      Name4  1               1

I tried joining them with left join with condition "on (sql1.Class = sql2.Class and sql1.Name = sql2.Name)" but the result set doesn't include Name4 record. I'm not good with left/right/outer joins

Any ideas?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 07:14:49

i gues this is what you want...


declare @table1 table (class int,name varchar(50),completed int)
insert into @table1
select 9,'name1',1 union all
select 9,'name2',1 union all
select 9,'name3',1

declare @table2 table (class int, name varchar(50),not_completed int)
insert into @table2
select 9,'name1',1 union all
select 9,'name4',1


select coalesce(t1.class,t2.class),coalesce(t1.name,t2.name),coalesce(t1.completed,0),coalesce(t2.not_completed,0)
from @table1 t1
	full outer join 	@table2 t2 on t1.class = t2.class and t1.name = t2.name

except... why does Name4 have a completed count of 1 in your example?

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 07:59:17

[code]select t.Class,t.Name,sum([Completed Count]),sum([Not Completed Count])
from
(
select Class, Name, [Completed Count],0 as [Not Completed Count]
FROm table1

union all

select Class, Name,0, Not Completed Count
from table2)t
GROUP BY t.Class,t.Name[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources