sql query with multiple where statements
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 >= '55') OR (meta_key = 'lat' AND meta_value <= '65') ) AND ( (meta_key = 'long' AND meta_value >= '20') OR (meta_key = 'long' AND meta_value <= '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
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
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 >= 55 AND m1.meta_value <= 65 AND m2.meta_key = 'lng' AND m2.meta_value >= 20 AND m2.meta_value <= 30
Read more... Read less...
( (meta_key = 'lat' AND meta_value >= '60.23457047672217') OR (meta_key = 'lat' AND meta_value <= '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
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
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 >= '60.23457047672217') OR (meta_value <= '60.23457047672217') ) ) OR ( (meta_key = 'long') AND ( (meta_value >= '24.879140853881836') OR (meta_value <= '24.879140853881836') ) ) GROUP BY item_id
OR between the top-level conditionals. This is because you want records which are
long, since no single record will ever be
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.
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.