how to apply where condition for only one element inside the IN statement

Question

I have a SQL query which will return product list and it's respective sales. Below is the query

SELECT *
FROM sales
where sales.name IN ('product_1', 'product_2')
  AND sales.product_number IN (SELECT number FROM products) where sales.name = 'product_2'

There are some unnecessary rows in product_2 which i want to filter out with the help of prod_number.

For example the output of above query is
cust_id   name      product_number
1        product_1   11
2        product_2   22
3        product_2   23
4        product_2   34

Now i want to filter out the product_2 rows based on it's product_number. I want product_2 only with product_number 22 and 23. I tried the above query but it's returning an error.

Answer 1

Use an OR condition to deal with the two cases.

SELECT *
FROM sales
WHERE (name = 'product_1' AND product_number IN (SELECT number FROM products))
   OR (name = 'product_2' AND product_number IN ('22', '23'))

Since MySQL often optimizes OR poorly, you may get better results if you split this into two queries that you combine with UNION.

SELECT s.*
FROM sales AS s
JOIN products AS p ON s.product_number = p.number
WHERE s.name = 'product_1'

UNION ALL

SELECT *
FROM sales
WHERE name = 'product_2' AND product_number IN ('22', '23')