Choose a parent if all children meet the criteria

Question

I have tables configured like this:

Parent ------ id, ... Child ----- id, parent_id, x, y 

I want to find Parents or a separate parent_id (s) if all the rows in Child containing the given parent_id satisfy the criteria related to 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 

will result in 3. I currently have a query that finds parent_ids that any of the children matches the criteria. Then I use this to get these records and check them in the code if all the children meet the criteria. With the sample data, I get parent_id 2 and 3, get two parent records with all the children, and evaluate. I want to do this with a single request, if possible.

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 cx <> cy ) 

Edit: here is the sql script: http://sqlfiddle.com/#!3/20128/1/0

Answer 2

That's what you need?

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

Answer 3

Two tables need to be joined first, because parents do not have children who will satisfy

And should add an index to the 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 cx <> cy )