Re: joining a query with a select count(*)

I think that something like this should work for you

SELECT child_pk, coalesce(cnt, 0)
FROM childtable c
left outer join (
SELECT child_fk, count(*) as cnt
FROM grandchildtable
GROUP BY child_fk
) t
ON (c.child_pk= t.child_fk);

IMHO, if Postgres had Oracle's (+) notation these would be a lot easier...

John

Matthew Terenzio wrote:
>
> I want a query to return all the rows from one table along with a count
> of rows in another table that reference each returned row from the first
> table.
>
> For instance, if you had a table of children and another table of
> grandchildren that had a foreign key on the children table, I'd want to
> return all children with a count of the number of kids they had.
>
> I hope I can figure it out before I get a reply.
>
> Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org