Interesting SQL Problem

[VernMan]

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

[Newsgroup_User · Accepted solution]

.oO(VernMan)

>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

Try

SELECT ...
FROM ...
WHERE ...
HAVING distance < 26
ORDER BY ...

Micha

[VernMan]

That did it thanks!!!