Как сделать этот запрос правильно?

Question

[vism]

Дано. (урезанный вариант)
Orders:
id
OrderProducts:
id
order_id
product_id
delivery_complete
Products:
id
category_id

В обоих условиях OrderProducts не должны урезаться из выборки(из join) как по ним идет калькуляция общенй суммы и т.п.
1) Простой вариант
Нужно получить все заказы, у которых все продукты delivery_complete=1
2) сложный вариант
обязательное условие, чтоб у заказа все продукты с products.`category_id` != 9 были orders_products.delivery_complete = 1
и при этом хотя бы один
orders_products.delivery_complete = 0 AND products.`category_id` = 9

Решил вопрос так(сложный вариант), но этот пипец коряво

SELECT 
  `orders`.*,
  `products_count`,
  `products_available`,
  `products_ek_count`,
  `products_ek_sum`,
  `products_in_stock`,
  (SELECT 
    COUNT(orders_products.id) 
  FROM
    orders_products 
    LEFT JOIN products 
      ON orders_products.`products_id` = products.id 
  WHERE orders_products.orders_id = `orders`.`id` 
    AND orders_products.delivery_complete = 1 
    AND products.`category_id` != 9) AS delivery_complete_not_ww_products,
  (SELECT 
    COUNT(orders_products.id) 
  FROM
    orders_products 
    LEFT JOIN products 
      ON orders_products.`products_id` = products.id 
  WHERE orders_products.orders_id = `orders`.`id` 
    AND products.`category_id` != 9) AS not_ww_products 
FROM
  `orders` 
  INNER JOIN `orders_products` 
    ON `orders_products`.`orders_id` = `orders`.`id` 
  INNER JOIN `products` 
    ON `orders_products`.`products_id` = `products`.`id` 
  INNER JOIN 
    (SELECT 
      orders_id,
      COUNT(id) AS products_count,
      SUM(delivery_complete) AS products_available,
      SUM(ek) AS products_ek_sum,
      SUM(IF(in_stock > 0, 1, 0)) AS products_in_stock,
      SUM(IF(ek > 0, 1, 0)) AS products_ek_count 
    FROM
      orders_products 
    GROUP BY orders_id) AS sq_products_count 
    ON `orders`.`id` = `sq_products_count`.`orders_id` 
GROUP BY `orders`.`id`,
HAVING delivery_complete_not_ww_products = not_ww_products 
  AND products_count > not_ww_products 
  AND products_count > products_available 
  AND not_ww_products > 0

UPDATE
легкий вариант готов

SELECT 
  `orders`.* 
FROM
  `orders` 
  INNER JOIN `orders_products` ON `orders_products`.`orders_id` = `orders`.`id` 
  INNER JOIN `products` ON `orders_products`.`products_id` = `products`.`id` 
WHERE `orders`.status NOT IN ('ERLEDIGT', 'STORNO') 
  AND orders.id IN (SELECT `orders_id` 
    FROM `orders_products`
    INNER JOIN `products` ON `orders_products`.`products_id` = `products`.`id`  
    WHERE orders_products.delivery_complete = 1 AND products.`category_id` != 9) 
GROUP BY `orders`.`id`

Но теперь нужно добавить условие сложного варианта

получается обязательное условие, чтоб у заказа все продукты с products.`category_id` != 9 были orders_products.delivery_complete = 1
и при этом хотя бы один
orders_products.delivery_complete = 0 AND products.`category_id` = 9

Answer 1

[Rsa97]

SELECT *
  FROM (
    SELECT `id` 
      FROM `Orders` 
      WHERE `id` NOT IN (SELECT `order_id` FROM `OrderProducts` WHERE `delivery_complete` != 1)
  ) AS `o`
  JOIN `OrderProducts` AS `op` ON `op`.`order_id` = `o`.`id`
  JOIN `Products` AS `p` ON `p`.`id` = `op`.`product_id`

Соответственно, меняете условие в NOT IN, так, чтобы во вложенном SELECT выбирались те продукты, которые НЕ соответствуют условию.

Comments

[vism]

Спасибо, в верное русло я свернул.
Вот что у меня получилось (простой вариант)

SELECT 
  `orders`.* 
FROM
  `orders` 
  INNER JOIN `orders_products` ON `orders_products`.`orders_id` = `orders`.`id` 
  INNER JOIN `products` ON `orders_products`.`products_id` = `products`.`id` 
WHERE `orders`.status NOT IN ('ERLEDIGT', 'STORNO') 
  AND orders.id IN (SELECT `orders_id` 
	  FROM `orders_products`
	  INNER JOIN `products` ON `orders_products`.`products_id` = `products`.`id`  
	  WHERE orders_products.delivery_complete = 1 AND products.`category_id` != 9) 
GROUP BY `orders`.`id`

Но не могу придумать как добавить второй условие
Чтобы при этом в заказе был минимум один продукт, с категорией 9 и delivery_complete=0
Тоесть тут нужен count и сранение по нему, если я правильно понимаю..

[vism]

Блин, не точность в формулеровке была у меня
для простого варианта(и как часть сложного)
Нужно отобразить заказы у которых все продукты удовлетворяют условию orders_products.delivery_complete = 1 AND products.`category_id` != 9
Т.е. если в заказе есть продукт products.`category_id` != 9 и orders_products.delivery_complete = 0. То не отображать. Наверное это и есть самая большая загвоздка для меня

[Rsa97]

vism, Так и поставьте в условие вложенного SELECT'a то, что не нужно отображать.

[vism]

Rsa97, не получается так.
смысл то в том, что если у заказа 5 продуктов
4 имеют категорию 5 и доставлены
1 имеет категорию 9 и не доставлен
то выбираем этот заказ

а если
4 имеют категорию 5 но 1 не доставлен

то не отображаем.

[Rsa97]

vism, Проще говоря, продукт должен быть либо доставлен, либо иметь девятую категорию. То есть

`OrderProducts`.`delivery_complete` = 1 || `products`.`category_id` = 9

Записываем отрицание:

!(`OrderProducts`.`delivery_complete` = 1 || `products`.`category_id` = 9) => 
!(`OrderProducts`.`delivery_complete` = 1) && !(`products`.`category_id` = 9) =>
`OrderProducts`.`delivery_complete` != 1 && `products`.`category_id` != 9

И заносим это в условие

...
      WHERE `id` NOT IN (
        SELECT `op`.`order_id` 
          FROM `OrderProducts` AS `op`
          JOIN `Products` AS `p` ON `p`.`id` = `op`.`product_id`
          WHERE `op`.`delivery_complete` != 1 AND `p`.`category_id` != 9)
...

[vism]

Rsa97, Не сработает
вот это я и не знаю как делать в SQL
По вашему условию я получу заказ, если хотя бы один продукт доставлен или имеет категорию 9

А нужно получить заказ если все 4 продукта с категорией !=9 доставлены и хотя бы один с категорией 9 не доставлен.

И я это заговнокодил так, но оно и работает медленно и не гибко

HAVING delivery_complete_not_ww_products = not_ww_products 
  AND products_count > not_ww_products 
  AND products_count > products_available 
  AND not_ww_products > 0

[Rsa97]

vism, Условие

WHERE `op`.`delivery_complete` != 1 AND `p`.`category_id` != 9

выберет все недоставленные продукты не девятой категории, а WHERE `id` NOT IN (SELECT ...), соответсвенно, все заказы, где таких продуктов нет. Полный запрос:

SELECT *
  FROM (
    SELECT `id` 
      FROM `Orders` 
      WHERE `id` NOT IN (
        SELECT `op`.`order_id` 
          FROM `OrderProducts` AS `op`
          JOIN `Products` AS `p` ON `p`.`id` = `op`.`product_id`
          WHERE `op`.`delivery_complete` != 1 AND `p`.`category_id` != 9)
  ) AS `o`
  JOIN `OrderProducts` AS `op` ON `op`.`order_id` = `o`.`id`
  JOIN `Products` AS `p` ON `p`.`id` = `op`.`product_id`

[vism]

Rsa97, Спасибо, на свежую голову разобрался.
NOT IN (`op`.`delivery_complete` != 1 AND `p`.`category_id` != 9) не то же самое что IN (`op`.`delivery_complete` = 1 OR `p`.`category_id` = 9)

Ваш запрос почти то, что нужно.
Но опять щас сидел думал и не придумал..
В вашем запросе попадают все продукты доставлены с категорией не 9.
Мне нужно как-то добавить условие, что обязательно должен быть НЕ доставленный продукт с категорией 9.

То есть в исключающей выборке нужно добавить условие что кол-во доставленных равно общему колличеству продуктов заказа?

SELECT `op`.`order_id` 
          FROM `OrderProducts` AS `op`
          JOIN `Products` AS `p` ON `p`.`id` = `op`.`product_id`
          WHERE `op`.`delivery_complete` != 1 AND `p`.`category_id` != 9
                    AND
               (
		SELECT COUNT(`op1`.id)
		FROM `OrderProducts` AS `op1`
		JOIN `Products` AS `p` ON `p`.`id` = `op1`.`product_id`
		WHERE `op1`.`delivery_complete` = 1 AND `op`.orders_id = op1`.orders_id
		) = (
		SELECT COUNT(`op2`.id)
		FROM `OrderProducts` AS `op2`
		JOIN `Products` AS `p` ON `p`.`id` = `op2`.`product_id`
		WHERE `op`.orders_id = op1`.orders_id
		)

Но этож бред да и не валидно.
Но как иначе это делается?

[Rsa97]

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

[vism]

в которых все продукты с категорией отличной от 9 доставлены
и при этом минимум один с категорией 9 не доставлен.

Первое условие вы помогли сделать, а как добавить второе не могу сообразить.

[Rsa97]

SELECT *
  FROM (
    SELECT `id` 
      FROM `Orders` 
      WHERE `id` IN (
        SELECT `op`.`order_id` 
          FROM `OrderProducts` AS `op`
          JOIN `Products` AS `p` ON `p`.`id` = `op`.`product_id`
            AND `op`.`delivery_complete` != 1 AND `p`.`category_id` = 9
        ) AND `id` NOT IN (
        SELECT `op`.`order_id` 
          FROM `OrderProducts` AS `op`
          JOIN `Products` AS `p` ON `p`.`id` = `op`.`product_id`
            AND `op`.`delivery_complete` != 1 AND `p`.`category_id` != 9
        )
  ) AS `o`
  JOIN `OrderProducts` AS `op` ON `op`.`order_id` = `o`.`id`
  JOIN `Products` AS `p` ON `p`.`id` = `op`.`product_id`

[vism]

Rsa97, все так как должно быть.
Спасибо огромное за столько уделенного времени.

Все довольно просто и элегантно оказывается.
С вашей точки зрения мои ошибочные пути мышления вызваны редкой практикой SQL или слабыми знаниями в алгебре логики?

[Rsa97]

vism, На мой взгляд, вам не хватает опыта в формализации задачи, то есть представления её в виде однозначного набора условий или выражений.

[vism]

Rsa97,
Судя по тому, что в вашем запросе все на базовом MySql, проблема в алгебре логики.

Спасибо за помощь.