Thread: 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/
> 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:
>
> 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