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 Quote 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 Quote 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. Quote 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 Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.