sql query with multiple where statements

Question

I am having a rather  for me  complicated mysql query on which I am totally stuck and cannot find any answer for online   Here s my query   SELECT items   FROM items INNER JOIN items meta data WHERE            meta key    lat  AND meta value  gt    55       OR          meta key    lat  AND meta value  lt    65     AND            meta key    long  AND meta value  gt    20       OR          meta key    long  AND meta value  lt    30     GROUP BY item id   Of course I have tested the query with only 1 statement and that works fine  So if I only pass the long or lat part then I get results  Only when I try to stitch them together I get different results   Thanks for the help in advance   The table structure is as follows   Table items  ID item name item description  Table meta  meta id item id meta key meta value  Solution  To whoever is interested I finally managed to tackle this problem  Thank you all for your help and insides   SELECT SQL CALC FOUND ROWS items    FROM items INNER JOIN items meta ON  items ID   items meta post id  INNER JOIN items meta AS m1 ON  items ID   m1 post id  WHERE 1 1 AND items post type    post  AND  items post status    publish   AND    items meta meta key    lat  AND CAST items meta meta value AS SIGNED  BETWEEN  55    AND  65   AND  m1 meta key    long  AND CAST m1 meta value AS SIGNED  BETWEEN  20  AND  30     GROUP BY items ID ORDER BY items date DESC

User · Answer

This               meta key    lat  AND meta value  gt    60 23457047672217       OR          meta key    lat  AND meta value  lt    60 23457047672217       is the same as             meta key    lat       Adding it all together  the same applies to the long filter  you have this impossible WHERE clause which will give no rows because meta key cannot be 2 values in one row  WHERE      meta key    lat  AND meta key    long      You need to review your operators to make sure you get the correct logic

User · Answer

You need to consider that GROUP BY happens after the WHERE clause conditions have been evaluated  And the WHERE clause always considers only one row  meaning that in your query  the meta key conditions will always prevent any records from being selected  since one column cannot have multiple values for one row   And what about the redundant meta value checks  If a value is allowed to be both smaller and greater than a given value  then its actual value doesn t matter at all - the check can be omitted   According to one of your comments you want to check for places less than a certain distance from a given location  To get correct distances  you d actually have to use some kind of proper distance function  see e g  this question for details   But this SQL should give you an idea how to start   SELECT items   FROM items i  meta data m1  meta data m2     WHERE i item id   m1 item id and i item id   m2 item id     AND m1 meta key    lat  AND m1 meta value  gt   55 AND m1 meta value  lt   65     AND m2 meta key    lng  AND m2 meta value  gt   20 AND m2 meta value  lt   30

User · Answer

Can we see the structure of your table  If I am understanding this  then the assumption made by the query is that a record can be only meta key -  lat  or meta key    long  not both because each row only has one meta key column and can only contain 1 corresponding value  not 2  That would explain why you don t get results when you connect the with an AND  it s impossible

User · Answer

What is meta key   Strip out all of the meta value conditionals  reduce  and you end up with this   SELECT   FROM meta data WHERE            meta key    lat     AND            meta key    long     GROUP BY item id   Since meta key can never simultaneously equal two different values  no results will be returned     Based on comments throughout this question and answers so far  it sounds like you re looking for something more along the lines of this   SELECT   FROM meta data WHERE        meta key    lat       AND                meta value  gt    60 23457047672217           OR          meta value  lt    60 23457047672217           OR        meta key    long       AND                meta value  gt    24 879140853881836           OR          meta value  lt    24 879140853881836           GROUP BY item id   Note the OR between the top-level conditionals   This is because you want records which are lat or long  since no single record will ever be lat and long   I m still not sure what you re trying to accomplish by the inner conditionals   Any non-null value will match those numbers   So maybe you can elaborate on what you re trying to do there   I m also not sure about the purpose of the GROUP BY clause  but that might be outside the context of this question entirely

[mysql] sql query with multiple where statements

Examples related to mysql