scotchegg78 Posted November 12, 2008 Share Posted November 12, 2008 HI Guys I have hte following... SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(52.478)) * SIN(RADIANS(job_postcodes.Lat)) + COS(RADIANS(52.478))* COS(RADIANS(job_postcodes.Lat)) * COS(RADIANS((-1.741)-job_postcodes.Long)))) as distance, PostCode FROM job_postcodes WHERE distance < 30 however I get the error distance unknown column?? Order by distance works, but not the where?? thanks for any info or help Link to comment https://forums.phpfreaks.com/topic/132408-solved-using-select-as-and-then-using-it-in-the-where/ Share on other sites More sharing options...
PFMaBiSmAd Posted November 12, 2008 Share Posted November 12, 2008 Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. To do what you want, you need to use a HAVING clause - ...HAVING distance < 30 Link to comment https://forums.phpfreaks.com/topic/132408-solved-using-select-as-and-then-using-it-in-the-where/#findComment-688370 Share on other sites More sharing options...
scotchegg78 Posted November 12, 2008 Author Share Posted November 12, 2008 thanks for the reply, i will try it, but i have it working now using this... SELECT sub.distance,jp.PostCode FROM (SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(52.478)) * SIN(RADIANS(jp.Lat)) + COS(RADIANS(52.478))* COS(RADIANS(jp.Lat)) * COS(RADIANS((-1.741)-jp.Long))))as distance FROM job_postcodes jp) sub, job_postcodes jp WHERE sub.distance < 20 however it takes 1.9 seconds , which i think is a bit long. Link to comment https://forums.phpfreaks.com/topic/132408-solved-using-select-as-and-then-using-it-in-the-where/#findComment-688375 Share on other sites More sharing options...
scotchegg78 Posted November 12, 2008 Author Share Posted November 12, 2008 yeah having will do it lol works in 0.03 secs Link to comment https://forums.phpfreaks.com/topic/132408-solved-using-select-as-and-then-using-it-in-the-where/#findComment-688377 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.