query with outer left join join

Posted by Sahoo under Sql Server on 6/10/2013 | Points: 10 | Views : 1673 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts