Posted by Hilarion on 09/20/05 13:46

> Can someone help a relative newcomer to SQL? (I dont think its a php
> specific question but I am trying it in php).
>
> I have two tables with a one to many relationship. I want to write a
> query that will tell me how many records exist in the parent table for
> which at least one record exists in the child table.
>
> My current query (Select count) gives me the count of records in the
> child table (ie the number of valid joins) - but I want to know the
> number in the parent table regardless of how many records in the child
> table (but must be at least 1).


In another words: you want to count how many parents have children?

SELECT count(*)
FROM parent_table
WHERE parent_table.parent_id IN (SELECT child_table.parent_id FROM child_table)

or

SELECT count(DISTINCT child_table.parent_id)
FROM child_table


Hilarion

PS.: In the second case I assumed that the relationship is enforced, so there
are no children that do not have valid parent_id set.

[Back to original message]