Exclude values from results if a column contains a specific value

Question

I am working with a SQL database where I receive the following data:

YEAR  | REFERENCE      | HEADER    | USER       | DATE        | ID            | Key      |
2019    Charity Funding              UCRP511      2020-01-15    0128923961US    
2019    Charity Funding              UCRP511      2020-01-15    0128923961US    JBOT_P10
2019    Charity Funding              UCRP511      2020-01-15    0128923961US    
2019    Charity Funding              UCRP511      2020-01-15    0128923961US    
2019    Charity Funding              UCRP511      2020-01-15    0128923961US    
2019    SJ/1019/233                  UCRP511      2020-01-10    
2019    Computer Comp                UCRP511      2020-01-15    024543342OS1    
2019    SJ/1019/231                  UCRP539      2020-01-10    
2019    PAYROLL (SC)    LABOR DIST   UCRP539      2020-01-10                    JBOT_P10

I have tried using the following solution:

Exclude rows with a column containing a value if multiple rows exist for

Here is the query I used:

SELECT DISTINCT *
FROM SDD.GenData s1
WHERE NOT EXISTS (
    SELECT 1
    FROM SDD.GenData s2
    WHERE s2.ID = s1.ID AND s2.Key LIKE '%BOT_P10'
)

Here are the rules of what I am looking for:

• If ID is NOT blank ('') then the row stays
• If ID is NOT blank ('') ,but has a value in "Key" for one or more rows of the same ID then all rows with said ID must all be excluded.
• If ID is blank ('') and has a value in "Key" then the row is excluded.
• If ID is blank and Key is blank then row stays.

Here is an example of what I am expecting based off the input above..

YEAR  | REFERENCE      | HEADER    | USER     | DATE        | ID              | Key      |
2019    Computer Comp                UCRP511    2020-01-15    024543342OS1  
2019    SJ/1019/233                  UCRP511    2020-01-10    
2019    SJ/1019/231                  UCRP539    2020-01-10    

Answer 1

This query will give you your desired result; the first part of the WHERE clause excludes rows with a given ID value if any of them have the excluded Key value, and the second includes rows where the ID value is blank, as long as the Key value is not the excluded value:

SELECT DISTINCT *
FROM GenData s1
WHERE NOT EXISTS (SELECT * 
                  FROM GenData s2
                  WHERE s2.ID = s1.ID AND s2.[Key] LIKE '%BOT_P10'
                  )
  OR (ID = '' AND NOT [Key] LIKE '%BOT_P10')

Output:

YEAR    REFERENCE       HEADER  USER        DATE        ID              Key
2019    Computer Comp           UCRP511     2020-01-15  024543342OS1     
2019    SJ/1019/231             UCRP539     2020-01-10           
2019    SJ/1019/233             UCRP511     2020-01-10          

Demo on SQLFiddle

Answer 2

You can use exists:

select t.*
from t
where (t.id is null and t.[key] is null) or
      (not exists (select 1
                  from t t2
                  where t2.id = t.id and t2.[key] is not null
                 ) and
       t.[key] is null
      );

You can also use window functions:

select t.*
from (select t.*,
             max(key) over (partition by id) as max_key
      from t
     ) t
where max_key is null or (id is null and [key] is null);

Here is a db<>fiddle.

Your rules are not quite as you state them. The rules appear to be:

• id is NULL and key is null; or
• There is no non-NULL key on a non-NULL id.