CODEWITHSUNDEEP
mysql
Tim
Tim

Reputation: 403

HAVING distance Greater than and Less than in the same Query

SELECT city, (6372.797 * acos(cos(radians({$latitude})) * cos(radians(`latitude_range`)) * cos(radians(`longitude_range`) - radians({$longitude})) + sin(radians({$latitude})) * sin(radians(`latitude_range`)))) AS distance FROM cities WHERE active = 1 HAVING distance > 25 ORDER BY distance ASC

I like to be able to grab all cities HAVING a distance greater than 25KM and less than 50KM. Anything I try entering either results in all cities greater than 25KM or an error.

How does one go about adding HAVING distance > 25 AND distance <= 50 to my SQL query?

Upvotes: 0

Views: 1426

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Exactly the way that you have in the question:

SELECT city, (6372.797 * acos(cos(radians({$latitude})) * cos(radians(`latitude_range`)) * cos(radians(`longitude_range`) - radians({$longitude})) + sin(radians({$latitude})) * sin(radians(`latitude_range`)))) AS distance
FROM cities
WHERE active = 1
HAVING distance > 25 and distance <= 50
ORDER BY distance ASC;

Just as a small note: the use of the having clause to filter on column aliases (like distance) is a MySQL extension. In most databases, you would have to use a subquery.

Upvotes: 8

Related Questions