sql query with multiple where statements

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

Leave a Comment