Return parent records with child records equal to specific values ​​AND where the total set of child records for a given parent is equal to a specific value

Question

I currently have a query that returns parent data for parent records that have a subset of child table entries equal to specific values. However, I want to narrow it down to only return parent records that have certain values, but where they are the only child records that belong to this parent, or if the number of child records does not exceed a given value.

Here is an example of a query that only gets me halfway to where I need it:

SELECT parent.item1, parent.item2, parent.index FROM parent INNER JOIN child on parent.index = child.index WHERE child.value IN (11111111, 33333333) GROUP BY parent.item1, parent.item2, parent.index HAVING COUNT(child.value) = 2 

Unfortunately, this query returns ANY parent data that has a subset of the identified values ​​included in the "IN" statement. I only need parent data for parent records whose full child records do not exceed a certain number (or, in my case, do not exceed the number of values ​​in the "IN" statement. Is there an easy way to accomplish this

Answer 1

Request for your request:

 SELECT parent.item1, parent.item2, parent.index FROM parent INNER JOIN child ON child.index = parent.index GROUP BY parent.item1, parent.item2, parent.index HAVING SUM(CASE WHEN child.value IN (1111111, 2222222) THEN 1 ELSE 0 END) = 2 AND COUNT(*) <= 2 

If you just want the children to match all the values ​​in the IN list and not one in the list, use this (in SQL Server )

 SELECT * FROM parent p WHERE NOT EXISTS ( SELECT NULL FROM ( SELECT value FROM child c WHERE c.index = p.index ) c FULL JOIN ( SELECT 11111111 AS value UNION ALL SELECT 22222222 ) q ON c.value = q.value WHERE c.value IS NULL OR q.value IS NULL )