Skip to content
Advertisement

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.

Advertisement

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.

5 People found this is helpful
Advertisement