SQL query that looks at multiple rows for a column value


March 2019


10 time


I'm trying to craft a query that selects rows based off a repeat value in a column

Row 1:             Row 2:            Row 3:           Row 4:
Record_Key = 1     Record_Key = 1    Record_Key = 2   Record_Key = 3
Type = 'AED'       Type = 'ACD'      Type = 'AED'     Type = 'AED'

I only want to select rows that have a Record_Key of AED that don't have an ACD associated with that Record_Key.

So in my provided example, I only want to select Row 3 and Row 4.

I can't say:

Select * From Table Where Type != 'ACD'

because this would return Row 1, Row 3, and Row 4 when I only want Row 3 and Row 4. I can't wrap my head around structuring this query, or does SQL and my provided table not allow for this kind of select?

2 answers


You can use not exists :

select t.*
from table t
where t.type = 'AED' and 
      not exists (select 1 from table t1 where t1.record_key = t.record_key and t1.type = 'ACD');

use not exists

    select t1.* from tab t1
    where not exists ( select 1 from tab t2 where t1.Record_Key=t2.Record_Key
                                       and type='ACD')