Find group of records that match a certain criteri

Question

I have the following data:

ID --- ParentID --- DataValue  
1  ---    1     ---    A  
2  ---    1     ---    B  
3  ---    1     ---    C  
4  ---    4     ---    B  
5  ---    4     ---    C  
6  ---    6     ---    A  
7  ---    6     ---    B  
8  ---    6     ---    C  
9  ---    6     ---    D

For each group of records (grouped by ParentID), I would like to find all groups that do not have a record containing "A" as a DataValue

Since groups 1 and 6 do contain at least one record that has "A" as a DataValue, I would not want to see them. I would only like to see records 4 and 5 (which are a part of group 4) since there are no records in this group that have an "A".

Any help is greatly appreciated!

Answer 1

SELECT
  ID,
  ParentID,
  DataValue
FROM
  MyTable
WHERE
  NOT EXISTS (
    SELECT 1 
      FROM MyTable i
     WHERE i.ParentId = MyTable.ParentId AND i.DataValue = 'A'
  )

An index over (ParentId, DataValue) is recommendable if the table is large.

Answer 2

Does this work? You don't say if all the data is in one table or not.

select parentid from yourtable
where groupid IN ( select DISTINCT groupid from yourtable where datavalue = 'A' )
group by parentid

Answer 3

Single pass solution:

select ParentID
from YourTable
group by ParentID
having sum(case DataValue when 'A' then 1 else 0 end) = 0

Answer 4

This should do

SELECT
  *
FROM
  yourtable
WHERE
  parentID NOT IN (SELECT DISTINCT parentID FROM yourtable WHERE DataValue = 'A')

Answer 5

I like Tomalak's answer, but I'm somehow skeptical that it will execute NOT EXISTS for each row in the table instead of for each distinct ParentId, so that when the groups are large, it will be slower than necessary.

In that case, Gaby's answer would be better, combined with an index on DataValue and an index on ParentId.

Just for fun, how about:

SELECT R.Id, R.ParentId, R.DataValue FROM (
    SELECT DISTINCT ParentId FROM YourTable
    EXCEPT SELECT DISTINCT ParentId FROM YourTable WHERE DataValue = 'A'
) L
LEFT JOIN YourTable R ON R.ParentId = L.ParentId

(Also with one index on DataValue and one index on ParentId)