query with outer left join join

Posted by Sahoo under Sql Server on 6/10/2013 | Points: 10 | Views : 715 | 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
Active 2,
Inactive 3,
Underdecision 0.

Thanks in advance


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

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

;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
select statusname,0 from @Status where statusid not in (select statusid from cte)


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