0

Consider this SQL query:

SELECT * FROM 
( 
SELECT * FROM wp_postmeta
WHERE 
(
meta_key = 'latitude' AND meta_value *1 BETWEEN 47.3641471102 AND 47.3731524898) 
OR (meta_key = 'longitude' AND meta_value *1 BETWEEN 8.53253429117 AND 8.54583070883)
) 
AS PostMeta, wp_posts 
WHERE wp_posts.id = PostMeta.post_id order by post_id asc

It gives me all records that match either latitude between certain values OR the longitude between certain values. What I want to get is the records that match both values. But changing the 'OR' in the query to 'AND' will give me zero results.

I think I need to do a subquery of somekind but dont know how to do it tho.

Anyone?

moppedisco
  • 1
  • Look at [this question](http://stackoverflow.com/questions/16249731/mysql-select-query-with-multiple-conditions). Also, you're not getting exactly "markers within a radius", you need to further filter the results using the [Haversine formula](http://en.wikipedia.org/wiki/Haversine_formula) or similar. – DCoder May 11 '13 at 11:31

1 Answers1

0

You really just need to split your latitude sub query up from your longitude sub query. Now that I have more time, I'll rework the query more explicitly:

SELECT
    /* You're only interested in the wp_posts data, right? */
    /* You don't care about the data from wp_postmeta. */
    /* Only select the data you're actually going to use. */
    /* Ideally you'd specify each column in wp_posts that you're */
    /* going to use and ignore the rest */
    p.*
FROM
    wp_posts p
        JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'latitude' AND CAST(meta_value AS DECIMAL) BETWEEN 47.3641471102 AND 47.3731524898) lat ON p.id = lat.post_id
        JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'longitude' AND CAST(meta_value AS DECIMAL) BETWEEN 8.53253429117 AND 8.54583070883) long ON p.id = long.post_id
ORDER BY
    post_id ASC

... as no single wp_postmeta record will be both a latitude and a longitude, you can't (at least, not nearly as simply as this) match them both in the same WHERE clause. So instead, create two separate calls to the wp_postmeta table, one for latitude and one for longitude, and just force them both to match (hence the INNER JOIN, rather than a LEFT JOIN)

Jason
  • 13,606
  • 2
  • 29
  • 40