Соединение Mysql, где все строки соответствуют условию; второй запрос - где хотя бы одна строка соответствует условию (частичное совпадение)

#mysql

Вопрос:

Я пытаюсь создать два запроса для заказов в магазине. В принципе, у меня есть список заказов с продуктами. В каждом заказе может быть несколько продуктов, и каждый продукт может находиться в хранилище. Я хочу найти:

Заказы, в которых хранятся все продукты (таблица заказов объединяет таблицу order_products, где все найденные строки должны иметь значение in_storage равным 1 (true / false — это не количество)
Заказы, в которых есть хотя бы один продукт в хранилище (но не все; поэтому в объединенной таблице order_products должно быть как минимум два продукта, в которых хотя бы один находится в хранилище; если для обоих in_storage установлено значение 1 — условие не выполнено и не будет отображаться как результат)

Ожидаемый результат — список идентификаторов заказа (уникальный / сгруппированный).

Я создал очень простую версию в db fiddle:

https://www.db-fiddle.com/f/nfa2ooHt3FWmKQM9DmmPEA/0

 CREATE TABLE orders (
  id INT
);
INSERT INTO orders (id) VALUES (1);
INSERT INTO orders (id) VALUES (2);

CREATE TABLE order_products (
  id_product INT,
  id_order INT,
  in_storage INT
);
INSERT INTO order_products (id_product, id_order, in_storage) VALUES (1,1,1); # product in storage -order id 1
INSERT INTO order_products (id_product, id_order, in_storage) VALUES (2,1,0); # product not in storage - order id 1
INSERT INTO order_products (id_product, id_order, in_storage) VALUES (3,2,1); # product in storage - order id 2
INSERT INTO order_products (id_product, id_order, in_storage) VALUES (4,2,1); # product in storage - order id 2

#Query first - find orders WHERE has all products in storage 
#expected result  - order with id 2

#Query second - find orders WHERE has partial products in stronage - minimum one product marked as in_storage but not all

#expected result - order with id 1  (first product in storage; second not)

SELECT id FROM orders 
left join order_products on orders.id = order_products.id_order
where order_products.in_storage = 1
GROUP BY id

Большое спасибо за помощь!

1. Не решение, но я заметил: «по крайней мере, один находится в хранилище» => where order_products.in_storage > 0

2. Ах, я забыл упомянуть, что in_storage это 1/0, как true / false — это не количество. (это база данных клиентов, и я не могу изменить эту идею)

Ответ №1:

# 1. Вы можете сделать это, сравнив количество всех заказов с количеством заказов в хранилище. Если подсчеты одинаковы, все продукты находятся в хранилище.

 SELECT id
FROM orders AS o
INNER JOIN order_products AS op ON o.id = op.id_order
GROUP BY id
HAVING COUNT(*) = SUM(op.in_storage = 1)

# 2 то же самое, за исключением того, что количество не равно. Также добавьте проверку, чтобы число в хранилище было не менее 1.

 SELECT id
FROM orders AS o
INNER JOIN order_products AS op ON o.id = op.id_order
GROUP BY id
HAVING COUNT(*) > SUM(op.in_storage = 1) AND SUM(op.in_storage) >= 1

В обоих случаях вы должны использовать INNER JOIN вместо LEFT JOIN , поскольку вас не интересуют заказы, в которых нет продуктов.

ДЕМОНСТРАЦИЯ