Find group of records that match a certain criteria

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!

score 5 · Accepted Answer · answered Nov 10 '10 at 18:04

5

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.

answered Nov 10 '10 at 18:04

Tomalak

332,285
67
532
628

score 2 · Answer 2 · answered Nov 10 '10 at 18:04

2

This should do

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

answered Nov 10 '10 at 18:04

Gabriele Petrioli

191,379
34
261
317

I think this would be the fastest. – littlegreen Nov 11 '10 at 23:12

score 1 · Answer 3 · answered Nov 10 '10 at 18:29

1

Single pass solution:

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

answered Nov 10 '10 at 18:29

He wants the entire record returned (for Id 4 and 5). – Mike M. Nov 10 '10 at 21:41
@Mike M: he said he wanted the groups. – Nov 11 '10 at 11:08
Doh, I'm sorry Mark. It looks like he requests both at different points in his post. I tried to vote back up but it's been over 15 hours and I'm unable to. I will vote up another answer of yours. – Mike M. Nov 11 '10 at 13:35

Jay · Answer 4 · 2010-11-10T21:48:11.753

0

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

edited Nov 10 '10 at 21:48

answered Nov 10 '10 at 18:03

Jay

13,803
4
42
69

He wants all data returned, not only the parentid. And groupid doesn't exist. – littlegreen Nov 11 '10 at 23:14

score 0 · Answer 5 · answered Nov 11 '10 at 23:11

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)

Find group of records that match a certain criteria

5 Answers5

Linked