CodeWhisperer CodeWhisperer - 2 months ago 6
SQL Question

SQL JOIN multiple many-to-many tables

Using MySql, I will like to join multiple many-to-many tables (users_roles and roles_permissions) by a given user id.
But there is something wrong with my query because it gives an error as below.


#1054 - Unknown column 'users_roles.role_id' in 'on clause'


SELECT roles.name, permissions.name
FROM users_roles, roles_permissions
JOIN roles ON users_roles.role_id = roles.id
JOIN permissions ON roles_permissions.permission_id = permissions.id
WHERE users_roles.user_id = 1 AND roles_permissions.role_id = roles.id


My table structure inside PHPMyAdmin is as follow:

Table users_roles
id | user_id | role_id

Table roles_permissions
id | role_id | permissions_id

Table roles
id | name | description

Table permissions
id | name | description

Answer

I think you should use a set of proper join

SELECT roles.name, permissions.name
FROM users_roles 
JOIN roles ON users_roles.role_id = roles.id
JOIN roles_permissions on roles_permissions.role_id = users_roles.role_id
JOIN permissions ON roles_permissions.permission_id = permissions.id
WHERE users_roles.user_id = 1