Answered
Interesting SQL Problem
I am using the following SQL and this works fine to retrun me
5 random records from the entire database.
SELECT penpals.*, Round((((acos(sin((-74.1192*pi()/180)) * sin((lon*pi()/180)) + cos((-74.1192*pi()/180)) * cos((lon*pi()/180)) * cos(((40.9353 - lat)*pi()/180))))*180/pi())*60*1.1515)) as distance
FROM penpals
LEFT JOIN geozip ON penpals.zip = geozip.zip
WHERE geozip.zip IS NOT NULL
ORDER BY RAND() LIMIT 5
The round function is being used to calculate the miles between 2 zip codes in the US, based on longitide and latitude defined as a column named distance.
This works fine and gives me what I need. However if I add the following
AND distance < 26 after WHERE geozip.zip IS NOT NULL
I get the error
Unknown column 'distance' in 'where clause'
but if I try to the ORDER BY distance it works to sort it.
Anyone have any ideas? Thanks
SELECT penpals.*, Round((((acos(sin((-74.1192*pi()/180)) * sin((lon*pi()/180)) + cos((-74.1192*pi()/180)) * cos((lon*pi()/180)) * cos(((40.9353 - lat)*pi()/180))))*180/pi())*60*1.1515)) as distance
FROM penpals
LEFT JOIN geozip ON penpals.zip = geozip.zip
WHERE geozip.zip IS NOT NULL
ORDER BY RAND() LIMIT 5
The round function is being used to calculate the miles between 2 zip codes in the US, based on longitide and latitude defined as a column named distance.
This works fine and gives me what I need. However if I add the following
AND distance < 26 after WHERE geozip.zip IS NOT NULL
I get the error
Unknown column 'distance' in 'where clause'
but if I try to the ORDER BY distance it works to sort it.
Anyone have any ideas? Thanks
AdChoices