count with a join and where clause 3

[rico14d]

Hi,
im trying to report on 2 tables like the following

tblMain
MainId Name
1 a
2 b
3 c

tblSub
Id MainID Status
1 1 1
2 1 1
3 2 1
3 2 2

basically i want to show all the rows from the main table with a count of the rows in the sub table where that status = 1.

at the moment i have this which would display
SELECT Main.ID, Main.name, COUNT(Sub.ID) AS TheCount
FROM Main LEFT OUTER JOIN
Sub ON Main.USER_ID = Sub.USER_ID
WHERE (Sub.STATUS = 1)
GROUP BY Main.ID, Main.name

MainID Name Count
1 a 2
2 b 1

I need to show the third Main row with a count of zero but because i put in a where clause it wont count the zero records from the sub table.

any ideas?

 

[PWise]

try

SELECT     Main.ID, Main.name,sum(case isnull(sub.status,0) when 1 then 1 else 0 end )
from main
left join 
Sub 
ON Main.USER_ID = Sub.USER_ID
GROUP BY Main.ID, Main.name

 

[AlexCuse]

You want to push the where clause into a derived table, so it does not limit the results in the way you're experiencing.

OTTOMH:

SELECT Main.ID, Main.name, COUNT(Sub.ID) AS TheCount
FROM Main LEFT OUTER JOIN (
	select * from Sub where STATUS = 1
) Sub ON Main.USER_ID = Sub.USER_ID
GROUP BY Main.ID, Main.name

You may need to handle for nulls in result column "TheCount".

Hoep this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[bborissov]

IMO that is better:

SELECT Main.ID, Main.name, ISNULL(Sub.Cnt,0) AS TheCount
FROM Main 
LEFT JOIN (select USER_ID, COUNT(*) AS Cnt
                  from Sub
           where STATUS = 1
           GROUP BY USER_ID) Sub
     ON Main.USER_ID = Sub.USER_ID

You got a smaller derived table, and if you have index by Status and User_Id...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[rico14d]

Thanks guys, all 3 solutions worked and have given me some good ideas for problems i had in the past.

I couldn't see any performance difference for any of the 3 queries, they all were timed at 1 second using SQL Management Studio. If you know how i can test the performance more accurately let me know and i'll post results!

Cheers,
Rich