query with outer left join join

Posted by Sahoo under Sql Server on 6/10/2013 | Points: 10 | Views : 770 | Status : [Member] | Replies : 1
Hi all ,
I hv 2 table "Status" and "User"
Status table hv valuses Active,Inactive,Underdecision.
User table hv user related data with a column statusid(FK to status table) and countryid. I want to find out all user statsus under a country.

Let say for india we had 3 user of each status then result will b
Active 1,
Inactive 1,
Underdecision 1.

Let say for UK we had 5 user of with active 2 , Inactive 3 and Underdecision 0
then
Active 2,
Inactive 3,
Underdecision 0.

Thanks in advance




Responses

Posted by: Niladri.Biswas on: 6/10/2013 [Member] Platinum | Points: 25

Up
0
Down
Declare @Status table(statusid int , statusname varchar(50))

Insert into @Status values(1,'Active'),(2,'Inactive'),(3,'Underdecision')

Declare @User table(username varchar(10),countryid int, statusid int)
Insert into @User values
('user1',1,1),('user2',1,2),('user3',1,3),
('user4',5,1),('user5',5,1),('user6',5,2),('user7',5,2),('user8',5,2)

;with cte as(
Select s.statusid,s.statusname, cnt = COUNT(u.statusid)
from @User u
inner join @Status s
on u.statusid = s.statusid
where countryid =5
group by s.statusid,s.statusname)

select statusname,cnt from cte
union
select statusname,0 from @Status where statusid not in (select statusid from cte)



output

statusname cnt
Active 2
Inactive 3
Underdecision 0

Best Regards,
Niladri Biswas

Sahoo, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response