Reputation: 37
This is a followup to a previous question to a previous but in reverse of Find rows where value in column not found in another row
Given one table, Table1
, with columns Key1 (int), Key2 (int), and Type (varchar)
...
I would like to exclude any two rows where
Type
is equal to 'TypeA'
and Key2
is Null
that have a corresponding row in the table where
Type
is equal to 'TypeB'
and Key2
is equal to Key1
from another row.
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
6 26 TypeC
7 NULL TypeD
8 NULL TypeD
I would like to return all the rows except where Key=1 and Key=3 because those rows together meet the criteria of Type='TypeA'/Key2=NULL and does have a corresponding row with Type='TypeB'/Key1=Key2.
Upvotes: 1
Views: 122
Reputation: 26609
Here is a solution using not exists which should be faster than a left outer join (see: http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/ ).
SELECT *
FROM demo d1
WHERE NOT ((TYPE LIKE 'TypeA'
AND Key2 IS NULL
AND EXISTS
(SELECT 1
FROM demo d2
WHERE d2.TYPE='TypeB'
AND d2.Key2 = d1.key1))
OR (TYPE LIKE 'TypeB'
AND Key2 IS NOT NULL
AND EXISTS
(SELECT 1
FROM demo d2
WHERE d2.TYPE='TypeA'
AND d2.Key1 = d1.key2)));
You should have indexes on key1 and key2.
CREATE INDEX index_key1
ON demo (key1);
CREATE INDEX index_key2
ON demo (key2);
Upvotes: 1
Reputation: 24470
Try this: http://sqlfiddle.com/#!6/fffcb/2
select a.*
from demo a
left outer join demo b
on
(
b.key2 = a.key1
and a.[Type] = 'TypeA'
and b.[Type] = 'TypeB'
and a.Key2 is null
)
or
(
b.key1 = a.key2
and b.[Type] = 'TypeA'
and a.[Type] = 'TypeB'
and b.Key2 is null
)
where b.key1 is null
Upvotes: 1