Thread: Problems using count() with a join

Hi,  I have the following 2 tables:

Table groups:  g_id     int4  g_name   text

Table users:  u_id     int4  g_id     int4

What I want is to get a count of # of users in each group, even if there 
are no users in the group.

I have tried the following:
SELECT t1.g_name,count(t2.g_id)  FROM groups t1,users t2  WHERE t1.g_id = t2.g_id  GROUP BY t1.g_name;

But it only give the counts of users in each group if there actually are 
users in that group.
I want the count to be 0 if there are no users in the group.

Thanks.

Doug Younger <postgres@mindspring.com> writes:
> What I want is to get a count of # of users in each group, even if there 
> are no users in the group.

> I have tried the following:
> SELECT t1.g_name,count(t2.g_id)
>    FROM groups t1,users t2
>    WHERE t1.g_id = t2.g_id
>    GROUP BY t1.g_name;

> But it only give the counts of users in each group if there actually are 
> users in that group.
> I want the count to be 0 if there are no users in the group.

Try SELECT as above plus

... UNION   SELECT t1.g_name, 0   FROM groups t1   WHERE NOT EXISTS(SELECT * FROM users t2 WHERE t1.g_id = t2.g_id);

Grotty, I know, but we don't have outer joins yet...
        regards, tom lane