MySQL Query - multiple WHERE clause in 1 column

Question

can someone help me create a query based on the query below.

As you can see, I have a product with specifications and certain groups that are created in the interface. I know the problem, 1 column cannot be 2 values ​​at once, but I only need the products that are in these two groups.

To illustrate, product_specification_sid, id 2 3 and 4 are sizes and the remaining 8 ~ 11 are colors, so I would like to choose a product with 2 and 3.

The inner join of the double table is not an option, since groups (sizes, colors) may change in the future.

SELECT products.*, categories.*, manufacturers.* FROM products INNER JOIN product_categories ON product_category_pid = product_id INNER JOIN categories ON product_category_cid = category_id INNER JOIN manufacturers ON product_manufacturer = manufacturer_id INNER JOIN product_specifications ON product_specification_pid=product_id WHERE product_active = 1 AND ( product_specification_sid in (3) AND product_specification_sid in (8,9,6,7,10,11) ) GROUP BY product_id 

Answer 1

Instead, you can use the having clause.

 SELECT products.*, FROM products INNER JOIN product_categories ON product_category_pid = product_id INNER JOIN categories ON product_category_cid = category_id INNER JOIN manufacturers ON product_manufacturer = manufacturer_id INNER JOIN product_specifications ON product_specification_pid=product_id WHERE product_active = 1 GROUP BY product_id HAVING COUNT(CASE WHEN product_specification_sid in (3) THEN 1 END) > 0 AND COUNT(CASE WHEN product_specification_sid in (8,9,6,7,10,11) THEN 1 END) > 0 

Answer 2

As I understand it, you are looking for a product entry that has two matching product_specification entries matching certain conditions. Sounds to me like a direct solution:

 SELECT products.*, categories.*, manufacturers.* FROM products INNER JOIN product_categories ON product_category_pid = product_id INNER JOIN categories ON product_category_cid = category_id INNER JOIN manufacturers ON product_manufacturer = manufacturer_id INNER JOIN product_specifications ps1 ON ps1.product_specification_pid=product_id INNER JOIN product_specifications ps2 ON ps2.product_specification_pid=product_id WHERE product_active = 1 AND ps1.product_specification_sid in (3) AND ps2.product_specification_sid in (8,9,6,7,10,11) 

By the way, this group does not work. You must group everything that is not a population, and each table must have at least one column, so you have at least three non-aggregates. (Well, maybe MySQL has some extension here, but in standard SQL to be queried.)