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 

Answer 1

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