Re: Count the children

Thanks to all who replied. I am an absolute newbie here.

Of course, parent.name is not unique, so I use parent.id instead and
all is well. Except that I need to return columns from the parent
table such as name, phone etc.

I assume this can be done using a subquery, but have no idea how it
should be.

Any offers?

Mike
On 19/08/2009, at 12:14 AM, Mark Styles wrote:

> On Wed, Aug 19, 2009 at 12:04:13AM +0930, Michael Rowan wrote:
>> I need what sounds like a very simple query - but I'm darned if I can
>> figure it out.
>>
>> I have two tables, parent and child related by child.parent_id =
>> parent.id
>> The query should return, for each parent row, parent.name and
>> number of
>> child rows
>>
>> Can someone enlighten me, please?
>
> Assuming parent.name is unique:
>
> SELECT parent.name, count(*)
> FROM parent, child
> WHERE parent.key = child.parent_key
> GROUP BY parent.name;
>
> --
> Mark
> http://www.lambic.co.uk
>

Michael Rowan
mike(dot)rowan(at)internode(dot)on(dot)net

11 Kingscote Street
ALBERTON
South Australia 5014

tel 618 8240 3993