sqlsql-likesql-server-ce-4

Issue with NOT LIKE


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


Solution

  • SELECT DISTINCT(ItemID)
    FROM   Items 
    WHERE  ItemID NOT IN 
           (
               SELECT ItemID
               FROM   Items 
               WHERE Brand LIKE '%es%' 
           )