CODEWITHSUNDEEP
mysqlsqlwhere-clause
Bugra YUKSEL
Bugra YUKSEL

Reputation: 389

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:

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.

Upvotes: 31

Views: 134583

Answers (6)

Ajay Gupta
Ajay Gupta

Reputation: 2957

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.

Upvotes: 2

akashivskyy
akashivskyy

Reputation: 45180

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.

Upvotes: 10

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 52

Keith John Hutchison
Keith John Hutchison

Reputation: 5277

select unique red24.image_id from 
( 
    select image_id from `list` where style_id = 24 and style_value = 'red' 
) red24
inner join 
( 
    select image_id from `list` where style_id = 25 and style_value = 'big' 
) big25
on red24.image_id = big25.image_id
inner join 
( 
    select image_id from `list` where style_id = 27 and style_value = 'round' 
) round27
on red24.image_id = round27.image_id

Upvotes: 2

bobwienholt
bobwienholt

Reputation: 17610

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'

Upvotes: 16

Lock
Lock

Reputation: 5522

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

Upvotes: 4

Related Questions