MySQL using internal joins in a computed column alias

Question

I have a query like this:

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 it is, I keep getting:

 Unknown column 'distance' in 'on clause' 

But if I put User.distance instead of distance, I get:

 MySQL syntax error near.... 

Can I use the “this way” alias in the computed field? 10 * 10 is just a simple placeholder, because the calculation is much more complicated.

Answer 1

 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 

Answer 2

You are trying to use a column alias in an ON clause. The MySQL documentation talks about this situation:

The conditional_expr clause used with ON is a conditional form expression that can be used in the WHERE clause.

And also :

Standard SQL prohibits references to column aliases in the WHERE clause. This restriction is imposed because when the WHERE clause is computed, the column value may not yet be defined. For example, the following request is illegal:

However, you can use variables to evaluate the expression in the ON clause, and then use the value in the column list, for example:

 SELECT User.id, @dist 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 (@dist := 10*10) = L.mindistance 

Answer 3

To avoid having to do the calculations three times in the query, you can wrap the external computation in the FROM subselect, which will give you access to the alias name (where it was not available in the 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 performed only twice instead of three.

Answer 4

You cannot use derived values ​​in a query where the sentence - where is used to restrict records and which indexes to use - the derived values ​​cannot be used by the optimizer, so you need to filter out the final results.

not quite sure what you are 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