Return parent records with child records equaling specific values AND where total set of child records for a given parent equal a specific value

Question

Currently I have a query that returns parent data for parent records that have a subset of child table records equaling certain values. However, I want to narrow it to only return those parent records with children having certain values, but where those are the only child records belonging to given parent or where the number of child records don't exceed a given total.

Here's an example query that only gets me half way to where I need to be:

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's data that has a subset of the identified values included with the "IN" statement. I only want the parent's data for parent records whose total child records don't exceed a certain number (or, in my case, don't exceed the number of values in the "IN" statement. Is there an easy way to accomplish this?

Answer 1

The query as you asked for it is:

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 to ensure that children match all of the values in the IN list and none not 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
        )