CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Sesame
Sesame

Reputation: 3410

Find group of records that match a certain criteria

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!

Upvotes: 1

Views: 4005

Answers (5)

littlegreen
littlegreen

Reputation: 7420

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)

Upvotes: 0

Jay
Jay

Reputation: 14481

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

Upvotes: 0

user359040
user359040

Reputation:

Single pass solution:

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

Upvotes: 1

Tomalak
Tomalak

Reputation: 338406

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.

Upvotes: 5

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196286

This should do

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

Upvotes: 2

Related Questions