joining a query with a select count(*)
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
On Sat, Nov 05, 2005 at 01:10:13PM -0500, 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.
Quick and dirty, should give you the idea...
select *, (select count(*) from table2 where fkey=pkey) from table1;
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
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:
Show quoted text
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?