91

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
Improve this question
4
  • 1
    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? Commented Dec 27, 2011 at 14:36
  • 1
    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? Commented Dec 27, 2011 at 14:36
  • 3
    You're searching for a database row where meta_key is both 'lat' and 'long'. I'm not sure that's possible... Commented Dec 27, 2011 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. Commented Dec 27, 2011 at 14:37

4 Answers 4

Reset to default
56

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.

The meta_value checks given in your question also are redundant - 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.

But this hints at your problem - you actually want to check two separate rows of your meta table; 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, it shows how to join the item element with the two entries in the meta table that are needed (one with meta_key = 'lat' and one with meta_key = 'long'):

SELECT items.* FROM items i, meta m1, meta 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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
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
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.
11

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

Improve this answer

3 Comments

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: yes. it is true. Logic dictates your WHERE is wrong
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'
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.

Improve this answer

6 Comments

@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.
The query should match 2 rows from the meta table per item to be valid indeed.
@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.
@user1117774: I've elaborated further in my answer.
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?
|
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.

Improve this answer

1 Comment

I added a short description of the table structure in the first post. I should have done that earlier, thanks for mentioning it

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.