MySQL Query - multiple WHERE clause on 1 column

Question

could anyone help me build up a query based on the query bellow.

As you can see I have a product with specifications and certain groups which are build up in the front-end. I know the problem, 1 column can't be 2 values at once but I need only those products that are in those 2 groups.

To illustrate, product_specification_sid, id 2 3 and 4 are sizes and de rest 8 ~ 11 are colors, so I would like to select a product that has 2 and 3.

Inner joining the table double isn't an option since the groups (sizes, colors) may vary 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

You can use a having clause instead.

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're looking for a product record that has two matching product_specification records meeting certain conditions. Sounds to me like the straightforward solution is:

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, that "group by" won't work. You have to group by everything that isn't an aggregate, 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 that would be rquired.)