Exclude rows where value in column not found in another row

Question

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.

JohnLBevan · Accepted Answer · 2014-07-17 00:00:17Z

1

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

answered Jul 17, 2014 at 0:00

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Μenelaοs · Accepted Answer · 2014-07-17 00:59:41Z

1

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);

edited Jul 17, 2014 at 0:59

answered Jul 17, 2014 at 0:25

Μenelaοs

24.4k20 gold badges98 silver badges166 bronze badges

Collectives™ on Stack Overflow

Exclude rows where value in column not found in another row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest