MySQL using Inner joins on an alias of a calculated column

alias, group-by, mysql, sql

Solution

SELECT User.id, 10*10 as distance 
FROM USERS 
INNER JOIN 
( 
    SELECT Location.user_id, 
    min(10 * 10) as mindistance 
    FROM Location 
    GROUP BY Location.user_id 
 ) L ON User.id = Location.user_id AND L.mindistance =10*10

Problem

I have a query like so: ``` SELECT User.id, 10*10 as distance FROM USERS INNER JOIN ( SELECT Location.user_id, min(10 * 10) as mindistance FROM Location GROUP BY Location.user_id ) L ON Users.id = Location.user_id AND distance = L.mindistance ``` If I leave it as is, I keep getting: ``` Unknown column 'distance' in 'on clause' ``` But if I put User.distance instead of just distance, I get: ``` MySQL syntax error near.... ``` Can I not use alias' this way on a calculated field? The 10 * 10 is just a simple placeholder as the calculation is much more complex.

Original source

Related problems