Блог

У меня есть две таблицы. Продукты и функции. Продукты имеют соединение «один ко многим» с продуктами.

Я хотел бы найти все продукты, которые имеют обе функции «blue» и «green».

Единственные способы, которые я знаю, как это сделать, — это использовать множественные объединения или агрегаты.

Схема:

 CREATE TABLE products (
  id INT PRIMARY KEY
);

CREATE TABLE features (
  product_id INT,
  name VARCHAR(20)
);

INSERT INTO products (id) VALUES 
(1);

INSERT INTO features (product_id, name) VALUES 
(1, "blue"),
(1, "green");
 

Попытки:

 -- Desired solution
SELECT p.id FROM products p
  JOIN features f ON f.product_id = p.id
  WHERE f.name = 'blue' AND f.name = 'green';

-- Solution 1: Multiple joines
SELECT p.id FROM products p
  JOIN features f1 ON f1.product_id = p.id
  JOIN features f2 ON f2.product_id = p.id
  WHERE f1.name = 'blue' AND f2.name = 'green';

-- Solution 2: Aggregates
SELECT x.id, MAX(x.cnd_1) AS cnd_1, MAX(x.cnd_2) AS cnd_2 FROM (
  SELECT p.id, 
    CASE WHEN f.name = 'blue' THEN TRUE ELSE FALSE END AS cnd_1,
    CASE WHEN f.name = 'green' THEN TRUE ELSE FALSE END AS cnd_2
  FROM products p
  JOIN features f ON f.product_id = p.id
) x GROUP BY 1
HAVING(cnd_1 = TRUE and cnd_2 = TRUE);
 

SQL скрипка

Хотя технически у меня есть 2 рабочих решения. Первое решение плохо масштабируется, а второе решение — беспорядок. Я генерирую запросы с десятками этих условий.

Есть ли лучший способ сделать это?

Вы можете использовать

 SELECT p.id 
FROM products p
JOIN features f ON f.product_id = p.id
WHERE f.name IN ('blue', 'green')
GROUP BY p.id
HAVING COUNT(DISTINCT f.name) = 2;