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.

Solution 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.

Solution 2

SELECT a.image_id 
FROM list a
INNER JOIN list b
   ON a.image_id = b.image_id
   AND b.style_id = 25
   AND b.style_value = 'big'
INNER JOIN list c
   ON a.image_id = c.image_id
   AND c.style_id = 27
   AND c.style_value = 'round'
WHERE a.style_id = 24 
   AND a.style_value = 'red'

Solution 3

You will never get a result, it's a simple logic error.

You're asking your database to return a row which has style_id = 24 AND style_id = 25 AND style_id = 26. Since 24 is niether 25 nor 26, you will get no result.

You have to use OR, then it makes some sense.

Solution 4

This might be what you are after, although depending on how many style_id's there are, it would be tricky to implement (not sure if those style_id's are static or not). If this is the case, then it is not really possible what you are wanting as the WHERE clause works on a row to row basis.

WITH cte as (
  SELECT
    image_id,
    max(decode(style_id,24,style_value)) AS style_colour,
    max(decode(style_id,25,style_value)) AS style_size,
    max(decode(style_id,27,style_value)) AS style_shape
  FROM
    list
  GROUP BY
    image_id
)
SELECT
  image_id
FROM
  cte
WHERE
  style_colour = 'red'
  and style_size = 'big'
  and style_shape = 'round'

http://sqlfiddle.com/#!4/fa5cf/18

Solution 5

May be using this query you don't get any result or empty result. You need to use OR instead of AND in your query like below.

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

Try out this query.

View more solutions

Comments

[mdoyle]

Is there any dependency between style_id and style_value? That is, does style_id 24 mean "red", while style_id 25 means "big"?

[Bugra YUKSEL]

Yes, i have another table named "styles". And a style has 5-6 different options. For example: style_id 24 has 5 different options (red, green, yellow, ...).

[mdoyle]

Could you provide a SHOW CREATE TABLE list\G and SHOW CREATE TABLE styles\G, and also provide a few sample rows from the table styles?

[Bugra YUKSEL]

Table 1: images id - image_name 1 - abc 2 - def 3 - ghi ... Table 2: Styles id - style_name - style_values 1 - color - (red-green-blue-etc.) 2 - shape - (round, rect, etc.) I am collection style values as a serialized text in one column. Whenever i create an image i am selecting color from a selectbox and i insert this relation to table 3: Table 3: relations (as i mentioned in my post) id - image_id - style_id - style_value

[Lock]

Can you provide a sample of the expected returned data?

[Bugra YUKSEL]

I just only want to take image_id from association table. For example as i mentioned in my first post: I want to take "red, big and round" image_id. It means: 45 (according to table)

[mdoyle]

So style_id 24 is always color, style_id 25 is always size, and style_id 26 is always shape. I think I am following.

[Lock]

So with the above, would the query not return any rows, as style_id 26 does not equal 'rect'?

[Bugra YUKSEL]

Yes friend, you are right. style_id 24 is always color, style 25 is always size.

[Bugra YUKSEL]

@Lock, You are right. I have made a mistake while writing to stackoverflow. Now, i have fixed it. When style_id 27, it must be round.

[Bugra YUKSEL]

But, there're also both, image_id (45) => style_id (24), image_id (45) => style_id (25), image_id (45) => style_id (26)... It means that table have 3 different rows with same image_id.

[Bugra YUKSEL]

I have tried in sqlfiddle, but these values aren't static. So i have to convert to into a dynamic version. If i cannot find any practical solution, i can try your solution. Thanks Lock.

[akashivskyy]

So you should check also for the same image_id. I'm not quite sure what's the purpose of your SQL.

[Bugra YUKSEL]

thanks. Your code works very well. I just only remove "unique" parameter according my need. Everything is okay right now. Thanks again and have a nice work.

[Eric Haynes]

Never knew you could group where clauses like that. Nifty.