MySQL using Inner joins on an alias of a calculated column

Viewed 27063

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.

4 Answers
Related