Mysql + where clause matching multiple rows

Question

I have a table like this:

id image_id style_id ------------------------ 1 45 24 1 45 25 1 47 25 1 45 27 1 45 28

I want to pull image_id column where all three below conditions match:

style_id = 24 style_id = 25 style_id = 27

I have a query like this:

SELECT image_id FROM list WHERE (style_id = 24 AND style_id = 25 AND style_id = 27)

which doesn't return me image_id 45.

The query you've written doesn't make much sense - how can 1 row's column have 3 different values, i.e. be equal to 24, 25 and 27 at the same time? — radoh
– radoh, Commented Mar 15, 2016 at 14:46

Giorgos Betsos · Accepted Answer · 2016-03-15 14:42:09Z

Try this:

SELECT image_id FROM list WHERE style_id IN (24, 25, 27) GROUP BY image_id HAVING COUNT(DISTINCT style_id) = 3

The DISTINCT keyword is only necessary in case you can have duplicate values of style_id field per image_id.

1 Answer 1