Выбор объектов с использованием нескольких критериев из объекта ассоциации «многие ко многим»
У меня возникают проблемы с выбором объектов типа Продукт, если продукт имеет две или более категорий.
У меня есть следующие таблицы:
product
============
product_id (PK)
name
category
============
category_id (PK)
name
и их ассоциация:
product_category
============
product_id (PFK)
category_id (PFK)
Теперь проблема в том, что я не могу выбрать продукт в соответствии с несколькими category.category_id, используя таблицу product_category.
Например: я хочу выбрать все продукты, которые относятся к категории 1 и 2, это означает, что в таблице product_category есть два вхождения.
Чтобы выбрать все продукты с одной категорией, можно использовать следующий выбор
SELECT p.*
FROM product p
JOIN product_category pc using(product_id)
WHERE category_id = 1;
Но как я могу выбрать, где продукт имеет category_id 1 и 2? Запрос должен возвращать только продукты, в которых связаны обе категории.
WHERE category_id = 1 and category_id = 2;
Это явно невозможное совпадение, но как я могу обойти это? Это вообще возможно?
Ответы 1
Присоединяйтесь к производной таблице, в которой вы фильтруете все ассоциации на предмет принадлежности к любой из требуемых категорий. Затем сгруппируйте по продукту и используйте предложение HAVING, которое проверяет, соответствует ли количество различных категорий количеству требуемых категорий. Продукт, имеющий все желаемые категории, удовлетворит это.
SELECT p.*
FROM product p
INNER JOIN (SELECT pc.product_id
FROM product_category pc
WHERE pc.category_id IN (1, 2)
GROUP BY pc.product_id
HAVING count(DISTINCT pc.category_id) = 2)
ON pc.product_id = p.product_id;
Спасибо! Проблема решена, но я использовал внутренний выбор в предложении WHERE, например WHERE product_id в (SELECT...).