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!
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.
This should do
SELECT
*
FROM
yourtable
WHERE
parentID NOT IN (SELECT DISTINCT parentID FROM yourtable WHERE DataValue = 'A')
Single pass solution:
select ParentID
from YourTable
group by ParentID
having sum(case DataValue when 'A' then 1 else 0 end) = 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
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)
