cdub cdub - 6 months ago 22
SQL Question

MySQL using Inner joins on an alias of a calculated column

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.

Answer
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
Comments