CODEWITHSUNDEEP
sqlsql-server
user1266896
user1266896

Reputation: 37

Find rows where value in column not found in another row

Can't seem to make this SQL query work! I have searched for the answer to this and although some posts come close, they just miss the mark.

Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...

I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT 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

I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.

I have tried this and it doesn't work...

SELECT t1.Key1, t1.Key2, t1.Type
FROM Table1 t1
WHERE t1.Key2 IS NULL 
    AND t1.Type LIKE 'TypeA'
    AND t1.Key1 NOT IN
        (SELECT Key1
            FROM Table1 t2
            WHERE t1.Key1 = t2.Key2
                AND t1.Key1 <> t2.Key1
                AND t2.Type LIKE 'TypeB')

Upvotes: 0

Views: 107

Answers (1)

Twelfth
Twelfth

Reputation: 7180

I'm not the biggest fan of where subqueries.

select t1.Key1, t1.Key2, t1.Type
from table1 t1
left join table1 t2 
          on t1.key1 = t2.key2 
          and t2.type = 'typeb'
where t1.type = 'typea' 
      and t1.key2 is null 
      and t2.key1 is null

I think the logic there is right. We are taking table 1 where t1.key2 is null and t1.type = 'typea'...left joining it to itself as t2 where t2.type = 'typeb'. Every time it finds a t2.type b record, we want to omit it, so where t2.key1 (or any t2 field) is null.

Logic make sense? Give it a run and let me know

Upvotes: 1

Related Questions