Thread: Showing rows where COUNT() is 0

Showing rows where COUNT() is 0

From
"Scott A. Barron"
Date:
Hello all,

Given the following tables:

T1          T2                T3
-------------------------------------------
usid | c     sid | usid | c     sid | usid | r

where uid, sid, and r are int's, and c is text

I have the query:

SELECT t1.c, COUNT(t2.usid), AVG(t3.r)   FROM t1, t2, t3   WHERE t2.usid = t1.usid AND t3.sid = t2.sid   GROUP BY t1.c

Which gives me the total number of sid's in t2 belonging to t1.usid and the
average of t3.r for each t2.sid belonging to each t1.usid.

The query works fine exept it doesn't show output where COUNT() returns 0.  I
would like to list all of the results even if there is nothing in t2 to match
the usid in t1.  Is there a way I can make this happen?

Thanks,
Scott