|
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.
Navigation:
[Reply to this message]
|