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

6

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.

answered Mar 15, 2016 at 14:42

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

Collectives™ on Stack Overflow

Mysql + where clause matching multiple rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related