MySQL PHP - SELECT WHERE id = array()

Question

I have one table with following fields...

   id category_id product_id
    1     1         1001
    2     2         1001
    3     1         1003
    4     4         1001
    5     1         1005

Now i'm trying to fetch out product id using category id.

So suppose in my query string i pass www.demo.com/?category=1,2,4

I want to get product which match in all value(1,2,4). so in this case it should return me only 1001.

How can i achieve this?

Answer 1

You can use IN() in combination with GROUP BY and HAVING

SELECT
    product_id
FROM 
    your table
WHERE
    category_id IN (1, 2, 4)
GROUP BY
    product_id
HAVING
    COUNT(DISTINCT id) = 3;

Explanation

With COUNT (DISTINCT id) = you check that this product_id will be in every category you checked.

DEMO