T-SQL: How to return a result set that excludes values based on an Exclusion table

I’m using SQL Server 2016 and I want to return a result set which has been filtered based on an “Exclusion” table. The rows in exclusion table could contain a [Make] code, a [Color] code or a combination of [Make] & [Color] codes.

The data table has approx 450K rows.

Data table:
|  Id  |  Make       |   Color      |   
x------|-------------|--------------|
|   1  |  Audi       | Blue         | 
|   2  |  Audi       | Red          | 
|   3  |  Audi       | Grey         | 
|   4  |  BMW        | Black        |
|   5  |  BMW        | Red          |
|   6  |  BMW        | White        |
|   7  |  BMW        | Grey         |
|   8  |  Ford       | Red          |
|   9  |  Ford       | Grey         |

    
Exclusion table:
|  Id  |  Make       |   Color      |   
|------|-------------|--------------|
|   1  |  Ford       | NULL         | 
|   2  |  Audi       | Red          |
|   3  |  NULL       | Grey         |

NULL values in the above table should be treated like wildcards eg. for ExclusionID = 1, return all rows where [Make] = “Ford” regardless of the [Color] attribute. The Exclusion table will never contain two NULL values in any given row.

Results table:
|  Id  |  Make       |   Color      |   
|------|-------------|--------------|
|   1  |  Audi       | Blue         |  
|   4  |  BMW        | Black        |
|   5  |  BMW        | Red          |
|   6  |  BMW        | White        |

The only solution that I’m able to come up with is to populate the Exclusion table using the ID’s of all the products which must be excluded. However, this would create an exclusion table that consists of many rows which would not be very readable (I’d like to be able to readily identify which [Make] and/or [Model] codes are in the exclusion list).

Any ideas? Thanks in advance.

(edit: I have removed references to Parent & Child codes as this was not correct)

Answer

You can use not exists:

select d.*
from data d
where not exists (select 1
                  from exclusions e
                  where (e.make = d.make or e.make is null) and
                        (e.color = d.color or e.color is null)
                 );

Advertisement

Answer