Sql Server Global Blocked List and Item specific Allow List

This seems easy at first but after thinking about it for the day I’m stuck.

Imagine you have a global block list for veggies.

create table blocked_veggie(veggieid int primary key, veggiename varchar(20))

insert into blocked_veggie(veggieid, veggiename) values(1, 'broccoli')
insert into blocked_veggie(veggieid, veggiename) values(2, 'carrots')

This table says these veggies are never allowed at dinner.

However, there is also an override table for a specific child that says for this child ignore the global block and allow the veggie.

create table allow_veggie(allowuid uniqueidentifier primary key, veggiename varchar(20), childname varchar(20))

insert into allow_veggie(allowuid, veggiename, childname) values(newid(), 'broccoli', 'Jimmy')

Here is what the query looks like:

declare    @veggie varchar(20) = 'broccoli'
         , @childname varchar(20) = 'Jimmy'

select 1
from blocked_veggie v
where v.veggiename = @veggie

union all

select 1
from allow_veggie a
where a.veggiename = @veggie
and a.childname = @childname

How can I cancel out the first query where I find a hit in the second query? Put another way I would want an empty result set returned.

I can’t combine them because I could have this scenario…

declare    @veggie varchar(20) = 'broccoli'
         , @childname varchar(20) = null

select 1
from blocked_veggie v
where v.veggiename = @veggie

union all

select 1
from allow_veggie a
where a.veggiename = @veggie
and a.childname = @childname

Here there is no childname so there is not override of the global block.

Answer

I think we should use EXCEPT here, rather than UNION.

declare    
    @veggie varchar(20) = 'broccoli',
    @childname varchar(20) = 'Jimmy';

select v.veggiename
from blocked_veggie v
where v.veggiename = @veggie

EXCEPT

select a.veggiename
from allow_veggie a
where 
    a.veggiename = @veggie and 
    a.childname = @childname
;

If a @childname is NULL, the second part of the query would return no rows, because a.childname = NULL is false, and the overall query will return all rows from blocked_veggie.

Advertisement

Answer