Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Multiple Where Clause

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.

like image 319
Bugra YUKSEL Avatar asked Dec 26 '12 21:12

Bugra YUKSEL


People also ask

Can you have multiple WHERE clauses in MySQL?

MySQL allows you to specify multiple WHERE clauses. These clauses may be used in two ways: as AND clauses or as OR clauses. What is Operator? An operator is a special keyword used to join or change clauses within a WHERE clause.

Can we use 2 WHERE clause in SQL?

You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators.

Can you have multiple wheres in SQL?

You can use the OR condition in the WHERE clause to test multiple conditions where the record is returned if any one of the conditions are met. This example uses the WHERE clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition.

Can WHERE clause have multiple values?

The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.


1 Answers

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.

like image 61
fthiella Avatar answered Oct 04 '22 15:10

fthiella