SQL - unknown column in derived table

Question

This is the problematic part of my query:

SELECT
    (SELECT id FROM users WHERE name = 'John') as competitor_id,
    (SELECT MIN(duration)
        FROM
            (SELECT duration FROM attempts
                WHERE userid=competitor_id ORDER BY created DESC LIMIT 1,1
            ) x
    ) as best_time

On execution, it throws this error:

#1054 - Unknown column 'competitor_id' in 'where clause'

It looks like the derived table 'x' can't see the parent's query alias competitor_id. Is there any way how to create some kind of global alias, which will be usable by all derived tables?

I know I can just use the competitor_id query as a subquery directly in a WHERE clause and avoid using alias at all, but my real query is much bigger and I need to use competitor_id in more subqueries and derived tables, so it would be inefficient if I would used the same subquery more times.

score 0 · Answer 1 · answered Jun 06 '13 at 20:24

you may not need to use derived tables within the select statement, wouldn't the following accomplish the same thing?

SELECT
   users.id as competitor_id,
   MIN(duration) as best_time
FROM users
inner join attempts on users.id = attempts.user_id
WHERE name = 'John'
group by users.id

user2246674 · Answer 2 · 2013-06-06T20:36:25.360

There error is caused because a identifier introduced in a select output-clause cannot be referenced from anywhere else in that clause - basically, with SQL, identifiers/columns are pushed out and not down (or across).

But, even if it were possible, it's not good to write a query this way anyway. Use a JOIN between the users and attempts (on user id), then filter based on the name. The SQL query planner will then take the high-level relational algebra and write an efficient plan for it :) Note that there is no need for either a manual ordering or limit here as the aggregate (MIN) over a group handles that.

SELECT u.id, u.name, MIN(a.duration) as duration
FROM users u
-- match up each attempt per user
JOIN attempts a
ON a.userid = u.id
-- only show users with this name
WHERE u.name = 'John'
-- group so we get the min duration *per user*
-- (name is included so it can be in the output clause)
GROUP BY u.id, u.name

score 0 · Answer 3 · answered Jun 06 '13 at 21:05

Something about your query seems rather strange. The innermost subquery is selecting one row and then you are taking the min(duration). The min is unnecessary, because there is only one row. You can phrase the query as:

SELECT u.id as competitor_id, a.duration as best_time
from users u left outer join
     attempts a
     on u.id = a.userid
where u.name = 'John'
order by a.created desc
limit 1, 1;

This seems to be what your query is attempting to do. However, this might not be your intention. It is probably giving the most recent time. (If you are using MySQL, then limit 1, 1 is actually taking the second most recent record). To get the smallest duration (presumably the "best"), you would do:

SELECT u.id as competitor_id, min(a.duration) as best_time
from users u left outer join
     attempts a
     on u.id = a.userid
where u.name = 'John'

Adding a group by u.id would ensure that this returns exactly one row.

SQL - unknown column in derived table

3 Answers3