MySQL using aliases failed

February 3, 2022

There are 3 tables.

user_groups – List of user grousp (admin, user, …)
permissions – List of permissions when using the system, e.g. "access_user_management"
user_group_permissions – Assignment of user groups to permissions. For example, the group "admin" has the permissions "access_system", "access_user_management", "reset_system" while the group "user" has only the permission "access_system".

Now I want to join the three tables to get the list of permissions depending on the user group ID.

SELECT user_groups.user_group_id, user_group_permissions.permission_id, user_groups.name, permissions.name
FROM user_groups 
INNER JOIN user_group_permissions 
    ON user_groups.user_group_id = user_group_permissions.user_group_id
INNER JOIN permissions
    ON user_group_permissions.permission_id = permissions.permission_id
WHERE user_groups.user_group_id = 1
ORDER BY user_groups.user_group_id

My query is working well, but I want to use aliases for simplifying. Unfortunately aliases do not work.

SELECT user_groups.user_group_id, user_group_permissions.permission_id, user_groups.name, permissions.name
FROM user_groups ug
INNER JOIN user_group_permissions ugp
    ON ug.user_group_id = ugp.user_group_id
INNER JOIN permissions per
    ON ugp.permission_id = per.permission_id
WHERE ug.user_group_id = 2
ORDER BY ug.user_group_id

But then the following error is thrown:

Error Code: 1054. Unknown column ‘user_groups.user_group_id’ in ‘field list’.

Its the very same syntax for aliases that I saw in tutorials. Also tried it with as but this is also not working.

Anyone an idea why the aliases do not work?
I am using MySQL community server 8.0.28.

>Solution :

If you want to use an alias for a table, you have to replace every instance of the table name with said alias (except for the place where you set the alias, of course).

Like this:

SELECT ug.user_group_id, ugp.permission_id, ug.name, per.name
FROM user_groups ug
    INNER JOIN user_group_permissions ugp
        ON ug.user_group_id = ugp.user_group_id
    INNER JOIN permissions per
        ON ugp.permission_id = per.permission_id
WHERE ug.user_group_id = 2
ORDER BY ug.user_group_id