I have a table as follows:
ItemID
BrandID
with data as follows (I actually join to another table to get the Brand text but for brevity I have put them into the same table here
ItemID Brand
1 Test
1 Test2
1 stuff
2 test
3 guy
4 girl
5 rest
I do a contains search for the term 'es' as follows:
SELECT DISTINCT(ItemID) FROM Items WHERE Brand LIKE '%es%'
which returns Items (1, 2, 5).
I now want to do a does not contain which should return Items (3, 4) however the query
SELECT DISTINCT(ItemID) FROM Items WHERE Brand NOT LIKE '%es%'
also returns Item 1 as the third item 1 does not contain 'es'
so my question:
How can I do a Not Contains which makes sure that it only return Items where none of the records contain the given string?
I hope I have made myself clear on this.
I am using SqlServer Ce 4.0
SELECT DISTINCT(ItemID)
FROM Items
WHERE ItemID NOT IN
(
SELECT ItemID
FROM Items
WHERE Brand LIKE '%es%'
)