CODEWITHSUNDEEP
mysql
michaelT
michaelT

Reputation: 1711

MySQL using aliases failed

There are 3 tables.

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.

Upvotes: 1

Views: 117

Answers (1)

waka
waka

Reputation: 3417

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

Upvotes: 3

Related Questions