tonelm54
asked on
Where on a calculated field
Ive built the following SQL statement to work out the distance between 2 longitudes and latitudes:-
But on return it returns all rows from 'customer' even those which distance > 50, so it seems to be ignoring the 'distance' calculation.
/*Random Postcode*/
SET @Lat2=52.377702875828300;
SET @Lng2=-1.394046962293400;
SELECT `customer`.`custID`, `customer`.`postcode`, `postcodelatlng`.`latitude`, `postcodelatlng`.`longitude`,
(6378.135*ACOS(COS(RADIANS(90-`postcodelatlng`.`latitude`)) *COS(RADIANS(90-@Lat2)) +SIN(RADIANS(90-`postcodelatlng`.`latitude`)) *SIN(RADIANS(90-@Lat2)) *COS(RADIANS(`postcodelatlng`.`longitude`-@Lng2)))) AS 'distance'
FROM `customer`
LEFT JOIN `postcodelatlng` ON `customer`.`postcode` = `postcodelatlng`.`postcode`
WHERE 'distance' < 50 /*miles*/
But on return it returns all rows from 'customer' even those which distance > 50, so it seems to be ignoring the 'distance' calculation.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.