Add an inner join to mySQL GROUP_CONCAT statement
group-concat, mysql
Solution
try the following query
SELECT user.user_id, user.user, GROUP_CONCAT(roles.role_name separator ', ') roles
FROM user
JOIN user_roles ON user.user_ID = user_roles.user_ID
JOIN roles ON user_roles.role_ID= user_roles.role_ID
GROUP BY users.user_ID, users.user
Problem
Problem: I have a GROUP_CONCAT query that is working as intended, except I'd like to make the concat a joined answer, not the raw ID field. Current query: ``` SELECT user.user_id, user.user, GROUP_CONCAT(user_roles.roleID separator ', ') roles FROM user JOIN user_roles ON user.user_ID = user_roles.user_ID GROUP BY users.user_ID, users.user ``` Gives result: ``` +----------+---------+----------------------------+ | user_ID | user | roles | +----------+---------+----------------------------+ | 1 | Smith | 1, 3 | +----------+---------+----------------------------+ | 2 | Jones | 1, 2, 3 | +----------+---------+----------------------------+ ``` Desired result: ``` +----------+---------+----------------------------+ | user_ID | user | roles | +----------+---------+----------------------------+ | 1 | Smith | Admin, Other | +----------+---------+----------------------------+ | 2 | Jones | Admin, Staff, Other | +----------+---------+----------------------------+ ``` User table: ``` +----------+---------+ | user_ID | user | +----------+---------+ | 1 | Smith | +----------+---------+ | 2 | Jones | +----------+---------+ ``` *users_roles table:* ``` +----------+---------+ | user_ID | role_ID | +----------+---------+ | 1 | 1 | +----------+---------+ | 2 | 1 | +----------+---------+ | 2 | 2 | +----------+---------+ | 2 | 3 | +----------+---------+ | 1 | 3 | +----------+---------+ ``` roles table: ``` +----------+-----------+ | role_ID | role_name | +----------+-----------+ | 1 | Admin | +----------+-----------+ | 2 | Staff | +----------+-----------+ | 3 | Other | +----------+-----------+ ```