mysqlsqlsubquerymysql-error-1054

Getting error code 1054 when running sub query

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.

Solution

  • 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.