| Author |
Basic SQL question
|
|
| Chris Brownbill 2005-09-20, 7:56 am |
| 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).
Help much appreciated!
| |
| Hilarion 2005-09-20, 7:56 am |
| > 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.
| |
| Chris Brownbill 2005-09-21, 7:56 am |
| Hilarion wrote:
>
> 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.
Thank you. The nested Select concept was what I was missing!
|
|
|
|