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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL Multiple Where Clause

Examples related to mysql

Examples related to sql

Examples related to where-clause