sqlmysql

MYSQL using alias in the WHERE clause

I've read that you cannot use an ALIAS in a WHERE clause, but I still haven't a good alternative solution to achieve what I'm trying to do below. What can I do with my DISTANCE calculation so that it's available in the WHERE clause?

SELECT n.nid AS nid, location.name AS location_name, (6371.0 * ACOS(SIN((location.latitude * RADIANS(1))) * SIN((28.755925 * RADIANS(1))) + COS((location.latitude * RADIANS(1))) * COS((28.755925 * RADIANS(1))) * COS((location.longitude * RADIANS(1)) - (-81.346395 * RADIANS(1))))) AS distance
            FROM 
            node n
            LEFT JOIN location_instance ON n.vid = location_instance.vid
            LEFT JOIN location ON location_instance.lid = location.lid
            WHERE (( (n.status = '1') AND (n.type IN  ('locations')) AND (distance <= 100) ))
            ORDER BY distance
            LIMIT 10
Solution

  • MySQL extends the use of the HAVING clause, which can be used for this purpose. If the query is not an aggregation query, then HAVING still does filtering -- but it allows aliases.

    So, you can write:

    SELECT n.nid AS nid, l.name AS l, (6371.0 * ACOS(SIN((l.latitude * RADIANS(1))) * SIN((28.755925 * RADIANS(1))) + COS((l.latitude * RADIANS(1))) * COS((28.755925 * RADIANS(1))) * COS((l.longitude * RADIANS(1)) - (-81.346395 * RADIANS(1))))) AS distance
FROM node n LEFT JOIn
     location_instance li
     ON n.vid = li.vid LEFT JOIN
     location l
     ON li.lid = l.lid
WHERE n.status = 1 AND n.type IN ('locations') 
HAVING distance <= 100
ORDER BY distance
LIMIT 10;

    Notes: