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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
