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
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
This question is related to
mysql
This..
(
(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
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 >= '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
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.
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.
Source: Stackoverflow.com