MySQL Multiple Where Clause

Question

I have a table like this:

id  image_id  style_id  style_value
-----------------------------------
1   45        24        red
1   45        25        big
1   47        26        small
1   45        27        round
1   49        28        rect

I want to take image_id column if:

• style_id = 24 and style_value = red
• style_id = 25 and style_value = big
• style_id = 26 and style_value = round

I have make a query like this:

$query = mysql_query("SELECT image_id FROM list WHERE (style_id = 24 AND style_value = 'red') AND (style_id = 25 AND style_value = 'big') AND (style_id = 27 AND style_value = 'round')

But I couldn't get any result. When I make this sample with OR, it works well. But I have to do this with AND. Because I need image id s which are both "red, big and rect".

I have made lots of search with Google but couldn't fine any solution.

Answer 1

I think that you are after this:

SELECT image_id
FROM list
WHERE (style_id, style_value) IN ((24,'red'),(25,'big'),(27,'round'))
GROUP BY image_id
HAVING count(distinct style_id, style_value)=3

You can't use AND, because values can't be 24 red and 25 big and 27 round at the same time in the same row, but you need to check the presence of style_id, style_value in multiple rows, under the same image_id.

In this query I'm using IN (that, in this particular example, is equivalent to an OR), and I am counting the distinct rows that match. If 3 distinct rows match, it means that all 3 attributes are present for that image_id, and my query will return it.