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 >= '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

Can you clarify specifically what happens when you "stitch them together"? What does the data you're querying look like and what results do you get? — David, Dec 27 '11 at 14:36
We can assume this is not a bug in mysql, so you need to say what you expected, and what you instead observed. What data are you putting through the query? — tenfour, Dec 27 '11 at 14:36
You're searching for a database row where `meta_key` is both `'lat'` and `'long'`. I'm not sure that's possible... — lonesomeday, Dec 27 '11 at 14:37
Nothing happens the results are empty. No query error or nothing else. Also no results thou I know for sure that there is an entry with the exact values as specified above... so it should at least return that one row. — user1117774, Dec 27 '11 at 14:37

score 51 · Accepted Answer · edited May 23 '17 at 11:55

51

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

edited May 23 '17 at 11:55

Community

1
1

answered Dec 27 '11 at 14:44

codeling

11,056
4
42
71

That is probably where my mistake lays. I need to find the items within a distance of about 10km of the current location. For testing I added the current coordinates to the query. Those should be 5km smaller then current location and 5km bigger then current location. – user1117774 Dec 27 '11 at 14:52
Aaah, that's enlightening. But why then compare two times with the same value? What you will need is a double join, once for the lng and once for the lat value, I will try to work it into my answer – codeling Dec 27 '11 at 14:56
Yes I am so messed up after fighting with this for hours that I find it difficult to explain. Thank you for your patience! I changed my initial post query to match more what I am after... I will edit it a bit more to get a more complete view. – user1117774 Dec 27 '11 at 15:00

score 9 · Answer 2 · answered Dec 27 '11 at 14:37

9

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

answered Dec 27 '11 at 14:37

gbn

422,506
82
585
676

This is not true. Each item has multiple meta data rows in the meta table. So I am searching for items which match to multiple matches. – user1117774 Dec 27 '11 at 14:40
@user1117774: yes. it is true. Logic dictates your WHERE is wrong – gbn Dec 27 '11 at 14:42
Every item in table items has multiple metadata rows attached to it from the meta table. So for example item with ID = 1 has in the meta table the following rows attached to it: rowID = 1, itemID = 1, meta_key = lat, meta_value = '1234' rowID = 2, itemID = 1, meta_ket = long, meta_valye = '5678' – user1117774 Dec 27 '11 at 14:46

David · Answer 3 · 2011-12-27T15:02:48.140

4

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.

edited Dec 27 '11 at 15:02

answered Dec 27 '11 at 14:38

David

208,112
36
198
279

@user1117774: But no single row will match this query. Maybe you want the `AND` in the reduced version to be an `OR`? Update the question with the table structure and we can help build a query. – David Dec 27 '11 at 14:40
The query should match 2 rows from the meta table per item to be valid indeed. – user1117774 Dec 27 '11 at 14:53
@user1117774: Then you definitely want to use `OR` between the `lat` and `long` checks. Something like: `WHERE (key='lat' AND value=...) OR (key='long' AND value=...)`. You want to test the `lat` and `long` individually and return any row which matches either one, since no one row will ever match both. – David Dec 27 '11 at 14:57
@user1117774: I've elaborated further in my answer. – David Dec 27 '11 at 15:03
Thanks David. I am following you but... if you are on a certain location on the globe you always have a latitude and longitude location. If I would like to show the items within 10km's of your current location I would need to match that both the latitude and longitude values are between the current ones... right? – user1117774 Dec 27 '11 at 15:03
@user1117774: Yes, and the logical `OR` in this case will help with that. It sounds like you think the `AND` means something different than it actually does. You're trying to ask the database, "Give me the lat records AND the long records." That's not how the query engine works. You need to ask it, "Give me any record which is a lat OR is a long." Then further refine from there, such as, "Give me any record which is a lat AND within this given range, OR any record which is a long AND within this given range." The WHERE clause matches row-by-row, it knows nothing of a conceptual globe. – David Dec 27 '11 at 15:08

score 1 · Answer 4 · answered Dec 27 '11 at 14:40

1

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.

answered Dec 27 '11 at 14:40

Wes Crow

2,971
20
24

I added a short description of the table structure in the first post. I should have done that earlier, thanks for mentioning it – user1117774 Dec 27 '11 at 14:48

sql query with multiple where statements

4 Answers4