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?

VMai · Accepted Answer · 2014-07-17 14:21:31Z

5

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

edited Jul 17, 2014 at 14:21

answered Jul 17, 2014 at 14:14

VMai

10.4k9 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Conde · Accepted Answer · 2014-07-17 14:10:59Z

5

Use IN():

SELECT * FROM table WHERE category_id IN(1,2,4);

answered Jul 17, 2014 at 14:10

John Conde

220k100 gold badges464 silver badges504 bronze badges

1 Comment

hlscalon Over a year ago

This will not return just 1001 (as OP wants), but 1003 and 1005 too

Kickstart · Accepted Answer · 2014-07-17 14:26:45Z

1

Generally I would go with the solution above by VMai, but just to give you another option you can join the table against itself, one for each possible category_id:-

SELECT a.product_id
FROM table a 
INNER JOIN table b ON a.product_id = b.product_id
INNER JOIN table c ON b.product_id = c.product_id
WHERE a.category = 1
AND b.category = 2
AND c.category = 4

answered Jul 17, 2014 at 14:26

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

2 Comments

VMai Over a year ago

This will produce the desired results too. The main drawback of the multiple self join is that it's not as easy to create dynamically. I won't speculate about the performance of both approaches with big tables and longer lists.

Kickstart Over a year ago

I fully agree with your comment. It is possibly easier to understand but its main advantage is if a few other columns details were required and which could not be used in the GROUP BY. Performance issues would be massively influenced by indexes.

Collectives™ on Stack Overflow

MySQL PHP - SELECT WHERE id = array()

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related