CODEWITHSUNDEEP
t-sql
Steve Wash
Steve Wash

Reputation: 986

Find all records NOT in any blocked range where blocked ranges are in a table

I have a table TaggedData with the following fields and data

ID  GroupID  Tag    MyData
**  *******  ***    ******
1    Texas   AA01   Peanut Butter
2    Texas   AA15   Cereal
3    Ohio    AA05   Potato Chips
4    Texas   AA08   Bread

I have a second table of BlockedTags as follows:

ID  StartTag  EndTag
**  ********  ******
1     AA00     AA04 
2     AA15     AA15

How do I select from this to return all data matching a given GroupId but NOT in any blocked range (inclusive)? For the data given if the GroupId is Texas, I don't want to return Cereal because it matches the second range. It should only return Bread.

I did try left joins based queries but I'm not even that close.

Thanks

Upvotes: 0

Views: 35

Answers (2)

Atheer Mostafa
Atheer Mostafa

Reputation: 735

I Prefer the NOT EXISTS simply because it gives you more readability, usability and better performance usually in large data (several cases get better execution plans):

would be like this:

SELECT * from TaggedData
WHERE GroupID=@GivenGroupID
AND NOT EXISTS(SELECT 1 FROM BlockedTags WHERE Tag BETWEEN StartTag ANDEndTag)

Upvotes: 0

Artem Koshelev
Artem Koshelev

Reputation: 10607

create table TaggedData (
    ID int,
    GroupID varchar(16),
    Tag char(4),
    MyData varchar(50))

create table BlockedTags (
    ID int,
    StartTag char(4),
    EndTag char(4)
)

insert into TaggedData(ID, GroupID, Tag, MyData)
values (1, 'Texas',   'AA01',   'Peanut Butter')
insert into TaggedData(ID, GroupID, Tag, MyData)
values (2, 'Texas' ,  'AA15',   'Cereal')
insert into TaggedData(ID, GroupID, Tag, MyData)
values (3, 'Ohio ',   'AA05',   'Potato Chips')
insert into TaggedData(ID, GroupID, Tag, MyData)
values (4, 'Texas',   'AA08',   'Bread')

insert into BlockedTags(ID, StartTag, EndTag)
values (1,     'AA00',     'AA04')
insert into BlockedTags(ID, StartTag, EndTag)
values (2,     'AA15',     'AA15')

select t.* from TaggedData t
left join BlockedTags b on t.Tag between b.StartTag and b.EndTag
where b.ID is null

Returns:

ID          GroupID          Tag  MyData
----------- ---------------- ---- --------------------------------------------------
3           Ohio             AA05 Potato Chips
4           Texas            AA08 Bread

(2 row(s) affected)

So, to match on given GroupID you change the query like that:

select t.* from TaggedData t
left join BlockedTags b on t.Tag between b.StartTag and b.EndTag
where b.ID is null and t.GroupID=@GivenGroupID

Upvotes: 1

Related Questions