Search code examples
sqlsql-likesql-server-ce-4

NOT LIKE search on Link Table

I have a Model table with an ID and A Text Column:

ID    |    Description
=======================
1     |     Model A
2     |     Model B
3     |     Model C

I have an Items table with an ID and lots of other columns. These two tables are linked via an intermediary table call ItemModels with the following data:

ID    |    ItemID     |    ModelID
==================================
1     |       1       |       1
2     |       1       |       2
3     |       2       |       1
4     |       2       |       2
5     |       2       |       3
6     |       3       |       2

I want to search using the standard "Contains, Does Not Contain, Starts With, Ends With" methods.

If I do a "Contains", "Starts With" or "Ends With" search using the LIKE operator this works fine and I always get the correct results, however I have a problem when using the NOT LIKE operator:

If I want to return all items where the model description does not contain "C" (case insensitive) I thought simply of doing the following:

SELECT ItemID FROM ItemModels INNER JOIN Model ON ItemModels.ModelID = Model.ID WHERE Description NOT LIKE '%C%'

I want this query to return Items 1 and 3 as neither of them have any models that contain 'C' however this query will also return item 2 as it will hit the record with ItemModel.ID = 3 and say "That does not contain C so we want to return that!" which of course is undesired behaviour.

So my question is:

How can I do a NOT LIKE search that encompasses all records in a Link table?

ps. I hope I have made this clear as it took me hours to track this issue down and work out why it was happening. And even more hours trying to work out how the hell to fix it!

Solution

  • You don't want any of the items to match your condition. Think in terms of aggregation and a having clause:

    SELECT im.ItemID
FROM im.ItemModels im INNER JOIN
     Model m
     ON im.ModelID = m.ID
GROUP BY im.ItemId
HAVING SUM(CASE WHEN Description LIKE '%C%' THEN 1 ELSE 0 END) = 0;

    This query counts the number of models that match the item. The = 0 says that there are none. I like this approach because it is quite flexible. Using AND and OR you can put together complicated conditions, such as like '%a%' and '%b%' but not like '%c%'.