0

I have the following query

SELECT placetbl.id            as place_id, 
       placetbl.name          as place_name,    
       placetbl.url_picture   as place_url_picture, 
       placetbl.url_info      as place_url_info,    
       placetbl.address       as place_address, 
       placetbl.phone         as place_phone,   
       placetbl.type          as place_type,    
       SQRT(POWER(ABS(placetbl.lat - (-37.881663)), 2) + POWER(ABS(placetbl.lon - (145.16314)), 2)) as place_distance,  
       placetbl.lat           as place_lat, 
       placetbl.lon           as place_lon  
FROM placetbl   
WHERE placetbl.status = 1 
  AND place_distance <= 0.1 
ORDER BY place_distance ASC

It complaints that my WHERE place_distance is referring to an unknown column place_distance

Where, place_distance is some calculation as above is performed as SQRT of calculation. I could use it in the ORDER BY. But why can't I use it in the WHERE?

How could I refer to it in the WHERE?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Elye
  • 53,639
  • 54
  • 212
  • 474
  • 2
    Use HAVING. (MySQL special solution.) – jarlh Sep 23 '18 at 06:53
  • 2
    Logical query processing order -> `1.FROM 2.JOIN 3.WHERE 4.SELECT 5.ORDER BY` You need to wrap calculated value with subquery and filter at outerquery level. – Lukasz Szozda Sep 23 '18 at 06:53
  • 1
    I got the answer using ```SELECT placetbl.id as place_id, placetbl.name as place_name, placetbl.url_picture as place_url_picture, placetbl.url_info as place_url_info, placetbl.address as place_address, placetbl.phone as place_phone, placetbl.type as place_type, SQRT( POWER(ABS(placetbl.lat - (-37.881663)), 2) + POWER(ABS(placetbl.lon - (145.16314)), 2)) as place_distance, placetbl.lat as place_lat, placetbl.lon as place_lon FROM placetbl WHERE placetbl.status = 1 HAVING place_distance <= 0.1 ORDER BY place_distance ASC``` – Elye Sep 23 '18 at 07:02

0 Answers0