MySQL using Inner joins on an alias of a calculated column

Question

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 1

To avoid having to make the calculation three times in the query, you can wrap the outer calculation in a FROM subselect, which will give you access to the aliased field name (where it wasn't accessible in your original query):

SELECT a.*
FROM
(
    SELECT id, 10*10 AS distance
    FROM USERS
) a
INNER JOIN
(
    SELECT user_id,
    MIN(10 * 10) AS mindistance
    FROM Location
    GROUP BY user_id
) L ON a.id = L.user_id AND a.distance = L.mindistance

Here, the calculation is only done two times instead of three.

Answer 2

You can't used derived values in a query where clause - where is used to restrict records and which indexes to use - derived values can't be used by the optimizer so you need to filter the final results.

not quite sure what you're doing but try something like:

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
HAVING USERS.distance = L.mindistance