Find group of records that match multiple values

Question

I have the following data:

ID --- ParentID --- DataValue  
1  ---    1     ---    1  
2  ---    1     ---    2  
3  ---    1     ---    6  
4  ---    2     ---    1  
5  ---    2     ---    2  
6  ---    2     ---    4  
7  ---    3     ---    1  
8  ---    3     ---    3  
9  ---    3     ---    5

For each group of records (grouped by ParentID), I would like to find all groups that match all given values in DataValue, for example:

• for values (1,2) ParentID 1 and 2 would be returned
• for values (1,6) only ParentID 1 would be returned (ParentID 2 group doesn't contain 6)

I looked at this question, which is very similar but OP looks for groups that do not contain single value.

Any help is greatly appreciated!

Answer 1

You can do this with conditional aggregation:

select parentid 
from tablename
group by parentid
having sum(case when datavalue = 1 then 1 else 0 end) > 0 and
       sum(case when datavalue = 6 then 1 else 0 end) > 0

Another way is use exists:

select distinct parentid
from tablename t1
where exists(select * from tablename where parentid = t1.parentid and datavalue = 1) and
      exists(select * from tablename where parentid = t1.parentid and datavalue = 6)

Another way is counting distinct occurrences:

select parentid 
from tablename
where datavalue in(1, 6)
group by parentid
having count(distinct datavalue) = 2