MySQL correlated subquery: can't resolve name in outer table

Question

I need to get a list of users with its highest role name. Due to some circumstances one user has just one role. So I have some users with the same

CONCAT(first_name, last_name)

but with different ids. Highest role can be simply found just sorting all the user's roles in ascending order and getting just the first one. Thus I decided to use correlated subquery to get highest role id for appropriate user.

But on executing the following query

SELECT 
    u.id                    AS UserID,
    u.user_name             AS UserName,
    u.user_hash             AS Hash,
    u.first_name            AS FirstName,
    u.last_name             AS LastName,
    u.phone_mobile          AS PhoneMobile,
    u.address_city          AS City,
    u.address_state         AS State,
    ar.name                 AS RoleName
FROM
    users AS u
        JOIN
    acl_roles_users AS aru ON (u.id = aru.user_id AND aru.deleted = 0)
        JOIN
    acl_roles AS ar ON aru.role_id = ar.id
        JOIN
            (SELECT 
                ar2.id AS RoleID
            FROM
                users AS u2
            JOIN acl_roles_users AS aru2 ON (u2.id = aru2.user_id
                and aru2.deleted = 0)
            JOIN acl_roles AS ar2 ON (aru2.role_id = ar2.id
                AND ar2.deleted = 0)
            WHERE
                concat(u2.first_name, u2.last_name) = concat(u.first_name, u.last_name)
            ORDER BY ar2.name ASC
            LIMIT 1) AS temptbl ON RoleID = ar.id
WHERE
    u.status = 'Active' and u.deleted = 0
ORDER BY UserName
LIMIT 1000000;

I get an error message

Error Code: 1054. Unknown column 'u.first_name' in 'where clause'

Why 'u' (users) table is not resolved in subquery? Are there any ideas how to rewrite query?

Jason Heo · Accepted Answer · 2014-07-17 12:51:51Z

1

Your query is not corelated subquery. In temptbl, u.first_name cannot be viewed.

How about using EXISTS:

SELECT 
    u.id                    AS UserID,
    u.user_name             AS UserName,
    u.user_hash             AS Hash,
    u.first_name            AS FirstName,
    u.last_name             AS LastName,
    u.phone_mobile          AS PhoneMobile,
    u.address_city          AS City,
    u.address_state         AS State,
    ar.name                 AS RoleName
FROM
    users AS u
        JOIN
    acl_roles_users AS aru ON (u.id = aru.user_id AND aru.deleted = 0)
        JOIN
    acl_roles AS ar ON aru.role_id = ar.id
WHERE
    u.status = 'Active' and u.deleted = 0
    AND EXISTS 
            (SELECT 
                1
            FROM
                users AS u2
            JOIN acl_roles_users AS aru2 ON (u2.id = aru2.user_id
                and aru2.deleted = 0)
            JOIN acl_roles AS ar2 ON (aru2.role_id = ar2.id
                AND ar2.deleted = 0)
            WHERE
                concat(u2.first_name, u2.last_name) = concat(u.first_name, u.last_name)
                AND ar2.id = ar.id
            ORDER BY ar2.name ASC
            LIMIT 1)
ORDER BY UserName
LIMIT 1000000;

answered Jul 17, 2014 at 12:51

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

erop Over a year ago

Unfortunately EXISTS section has NO effect on result set: your query version returns exactly the same result as without EXISTS clause.

lpg · Accepted Answer · 2014-07-17 12:51:06Z

0

You cannot get "u" fields in the subquery... Maybe you could try this:

SELECT 
    u.id                    AS UserID,
    u.user_name             AS UserName,
    u.user_hash             AS Hash,
    u.first_name            AS FirstName,
    u.last_name             AS LastName,
    u.phone_mobile          AS PhoneMobile,
    u.address_city          AS City,
    u.address_state         AS State,
    ar.name                 AS RoleName
FROM
    users AS u
        JOIN
    acl_roles_users AS aru ON (u.id = aru.user_id AND aru.deleted = 0)
        JOIN
    acl_roles AS ar ON aru.role_id = ar.id
        JOIN
            (SELECT 
                ar2.id AS RoleID
            FROM
                users AS u2
            JOIN acl_roles_users AS aru2 ON (u2.id = aru2.user_id
                and aru2.deleted = 0)
            JOIN acl_roles AS ar2 ON (aru2.role_id = ar2.id
                AND ar2.deleted = 0)
            ORDER BY ar2.name ASC
            LIMIT 1) AS temptbl ON RoleID = ar.id 
            AND concat(temptbl.first_name, temptbl.last_name) = concat(u.first_name, u.last_name)
WHERE
    u.status = 'Active' and u.deleted = 0
ORDER BY UserName
LIMIT 1000000;

If this doesn't work for you, maybe you could provide a http://sqlfiddle.com/ with your table structure and some dummy data...

answered Jul 17, 2014 at 12:51

lpg

4,9371 gold badge18 silver badges16 bronze badges

2 Comments

erop Over a year ago

This time we just have Error Code: 1054. Unknown column 'temptbl.first_name' in 'on clause' error.

lpg Over a year ago

true; you should add the fields in the select... when you do "SELECT ar2.id AS RoleID", you should add "SELECT ar2.id AS RoleID, u2.first_name, u2.last_name"

Community · Accepted Answer · 2017-05-23 12:21:25Z

Since role names are kept in VARCHAR column and looks like

'01 Mega boss',
'02 Super Boss',
'03 Standard Boss',
'04 Micro Boss',
etc.

I just rewrite a little @Inos Heo version to get positive result:

SELECT u.id            AS UserID, 
       u.user_name     AS UserName, 
       u.user_hash     AS Hash, 
       u.first_name    AS FirstName, 
       u.last_name     AS LastName, 
       u.phone_mobile  AS PhoneMobile, 
       u.address_city  AS City, 
       u.address_state AS State, 
       ar.name         AS RoleName 
FROM   users AS u 
       JOIN acl_roles_users AS aru 
         ON ( u.id = aru.user_id 
              AND aru.deleted = 0 ) 
       JOIN acl_roles AS ar 
         ON aru.role_id = ar.id 
WHERE  u.status = 'Active' 
       AND u.deleted = 0 
       AND NOT EXISTS(SELECT 1 
                      FROM   users AS u2 
                             JOIN acl_roles_users AS aru2 
                               ON ( u2.id = aru2.user_id 
                                    AND aru2.deleted = 0 ) 
                             JOIN acl_roles AS ar2 
                               ON ( aru2.role_id = ar2.id 
                                    AND ar2.deleted = 0 ) 
                      WHERE  Concat(u2.first_name, u2.last_name) = 
                             Concat(u.first_name, u.last_name) 
                             AND ar2.id < ar.id 
                      ORDER  BY ar2.name ASC 
                      LIMIT  1) 
ORDER  BY Concat(lastname, firstname) 
LIMIT  1000000;

Pay attention to using NOT EXISTS and LESS THAN sign in subquery WHERE clause. I.e. filtering out rows that has role "less than" ones compared with the same from outer query.

Thank you, @Inos Heo, for pointing in right direction!

Collectives™ on Stack Overflow

MySQL correlated subquery: can't resolve name in outer table

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related