Mysql filter out groups containing unwanted items-CodePudding

I have 2 tables. One contains list of ordered products with some data. Another is just a list of orders with count of products in every order. I would like to select only orders (order_id) which must contain product_id 1 (may include other products) and none of individual product in order does not contain "Y" in product_data.

SELECT DISTINCT op.order_id
FROM order_products op
JOIN orders o ON o.order_id = op.order_id
WHERE o.product_count = 3 AND op.id = 1 
GROUP BY op.order_id

How to filter out orders that contain products with "Y" in product_data? Thank you. Expected result should be: order_id 2

order_products

order_id	product_id	product_data
1	1	x
1	2	x
1	3	Y
2	1	x
2	2	x
2	5	x
3	50	x

orders

order_id	count
1	3
2	3
3	1

CodePudding user response：

SQL deals in sets of rows. So think about sets of order_id values.

This subquery generates the set of order_id values that have the 'Y' in their product_data column.

      SELECT order_id FROM order_products WHERE product_data = 'Y';

Use that set to filter your results. Like this. Fiddle here.

SELECT DISTINCT op.order_id
  FROM order_products op
  JOIN orders o ON o.order_id = op.order_id
 WHERE op.product_id = 1
  AND o.order_id NOT IN (
      SELECT order_id FROM order_products WHERE product_data = 'Y');

You don't need both DISTINCT and GROUP BY in this query.

CodePudding user response：

Just use WHERE clause.

SELECT DISTINCT op.order_id
FROM order_products op
JOIN orders o ON o.order_id = op.order_id
WHERE o.product_count = 3 AND op.id = 1 
GROUP BY op.order_id WHERE op.product_data = 'Y'
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^