НЕ НРАВИТСЯ поиск по таблице ссылок

У меня есть таблица Model с идентификатором и текстовой колонкой:

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

У меня есть таблица Items с идентификатором и множеством других столбцов. Эти две таблицы связаны посредством промежуточной таблицы ItemModels со следующими данными:

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

Я хочу выполнить поиск с использованием стандартных методов "Содержит, не содержит, начинает с, заканчивается на".

Если я выполняю поиск "Содержит", "Начинает с" или "Заканчивается на" с помощью оператора LIKE, это работает отлично, и я всегда получаю правильные результаты, однако у меня есть проблема при использовании оператора NOT LIKE:

Если я хочу вернуть все элементы, в которых описание модели не содержит "C" (без учета регистра), я просто подумал о следующем:

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

Я хочу, чтобы этот запрос возвращал элементы 1 и 3, поскольку ни у одного из них нет моделей, содержащих "C", однако этот запрос также будет возвращать элемент 2, поскольку он попадет в запись с ItemModel.ID = 3 и скажет: "Это не содержит C поэтому мы хотим вернуть это! " что, конечно, является нежелательным поведением.

Поэтому мой вопрос:

Как я могу выполнить поиск NOT LIKE, который охватывает все записи в таблице ссылок?

пс. Надеюсь, я сделал это ясно, поскольку мне потребовались часы, чтобы отследить эту проблему и выяснить, почему это происходит. И еще больше часов пытается выяснить, как, черт возьми, это исправить!

1 ответ

Вы не хотите, чтобы какие-либо элементы соответствовали вашему состоянию. Подумайте в терминах агрегации и having оговорки:

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;

Этот запрос подсчитывает количество моделей, соответствующих этому элементу. = 0 говорит, что их нет. Мне нравится этот подход, потому что он довольно гибкий. Используя AND и OR вы можете комбинировать сложные условия, такие как "% a%" и "% b%", но не как "% c%".

licensed under cc by-sa 3.0 with attribution.