I have the next query for getting addresses within a given distance and given postal code. Distance is calculated, based upon longitude and latitude data.

In this example i have replaced the user-input for just values (lat=52.64, long=6.88 en desired distance=10km)

the query:

SELECT *,

ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2) + POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) AS distance

FROM lp_relations_addresses distance

WHERE distance < 10

ORDER BY `distance` DESC

gives unknown column distance as error message.

when leaving out the where clausule i get every record of the table including their calculated distance. In this case i have to fetch the whole table.

How do i get only the desired records to fetch??

Thanks in advance for any comment!

解决方案

You can't reference an alias in the select clause from another part of the sql statement. You need to put the whole expression in your where clause:

WHERE

ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2)

+ POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) < 10

A cleaner solution would be to use a sub-query to generate the calculated data:

SELECT *, distance

FROM (

SELECT *,

ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2)

+ POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) AS distance

FROM lp_relations_addresses

) d

WHERE d.distance < 10

ORDER BY d.distance DESC