Select parent if all children meet criteria

Question

I have tables set up like so:

Parent
------
id, ...

Child
-----
id, parent_id, x, y

I want to find the Parents, or the distinct parent_id(s), if all of the rows in Child containing a given parent_id meet a criteria involving x and y(in my case x = y).

For example:

Parent
------
id
 1
 2
 3

Child
id, parent_id, x, y
1,      1,     2, 3
2,      1,     3, 4
3,      2,     5, 5
4,      2,     6, 7
5,      3,     8, 8
6,      3,     9, 9

would result in 3. Currently, I have a query that finds parent_ids that any of the children meet the criteria. I then use that to retrieve those records and check them in code if all the children meet the criteria. With the example data, I get parent_id 2 and 3, get the two parent records with all children, and evaluate. I want to do this with a single query, if possible.

Chastain

can you pls tell what is ur expected output? – Adamina

@AnandPhadke, my expected output would be 3. – Chastain

Which DBMS are you using? PostgreSQL? Oracle? DB2? – Michellmichella

@a_horse_with_no_name, MS SQL Server. – Chastain

Which version of SQL Server? 2000, 2005, 2008, 2008 R2, 2012? Do you have multiple levels of parent/child or just that single level as shown in your example data? – Michellmichella

@a_horse_with_no_name, 2008 R2. – Chastain

@a_horse_with_no_name, there are actually multiple levels, but there is a view that condenses it to the two. – Chastain

Answer 1

You can use NOT EXISTS

SELECT id 
FROM Parent p
WHERE NOT EXISTS
(
   SELECT 1 FROM Child c
   WHERE c.parent_Id = p.id
   AND c.x <> c.y
)

Edit: Here's the sql-fiddle: http://sqlfiddle.com/#!3/20128/1/0

Incisive

I had a similar case, I needed get all parents but in a column show who meet the criteria in children. I use a LEFT JOIN ( SELECT 1 as a, p2.id FROM Parent p2 WHERE NOT EXISTS ( SELECT 1 FROM Child c2 WHERE c2.parent_Id = p2.id AND c2.test = 'f' ) AND EXISTS ( SELECT 1 FROM Child c2 WHERE c2.parent_Id = p2.id AND c2.test = 't' ) ) cOK ON cOK.id = p.id – Agler

Isn't there any way to achieve this without a subquery? Looks low performing on large data sets. – Occiput

Answer 2

This is what you need?

  select id from parent where id not in(
    select parent_id from child 
    where x<>y
    group by parent_id)

Adamina

Answer 3

Should join 2 tables first because the parents does not have children that will satisfy

And should add index for pa_id column

SELECT DISTINCT pa.id 
FROM pa INNER JOIN c ON c.pa_id = pa.id 
WHERE NOT EXISTS ( SELECT 1 FROM c WHERE c.parent_Id = p.id and c.x <> c.y )

Jacques

Answer 4

Old question, but I think it worth to give my 5 cents on this topic. I believe more efficient way is to use HAVING clause:

SELECT 
   Parent.id
FROM
   Parent 
   JOIN Child ON Child.parent_id = Parent.id
GROUP BY
   Parent.id
HAVING
   SUM( CASE WHEN Child.x = Child.y THEN 1 ELSE 0 END) = COUNT( * )

Jin