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

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 开发者_StackOverflowhaving 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?

---

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
        )