Getting error code 1054 when running sub query

Question

I'm trying to get the user with oldest account created using this:

SELECT a.ID
     , a.username 
  FROM users a 
  JOIN 
     ( SELECT MAX(date_created) 
        FROM other_info
     ) b 
    ON a.ID = b.ID; 

It's returning the following; `Error Code: 1054.

Unknown column 'b.ID' in 'on clause'`

I've looked though the [mysql-error-1054] tag and haven't been able to find a solution, I'm not even sure which part of the query is wrong.

Thanks.

Answer 1

To find the user with the first (oldest) date_created:

SELECT u.ID, a.username, b.date_created
FROM users a
INNER JOIN other_info b
ORDER BY b.date_created
LIMIT 1

BTW, instead of a, b etc, I'd rather chose table aliases that make sense, e.g. u for users.

Answer 2

Try This :

SELECT a.ID, a.username 
FROM users a INNER JOIN 
(SELECT top 1 id,date_created FROM other_info order by date_created desc) b 
ON a.ID = b.ID;

Answer 3

SELECT a.ID , a.username
, MAX(date_created) FROM users a INNER JOIN other_info b ON a.ID = b.ID;