Add the inner join to the mySQL GROUP_CONCAT statement

Question

Problem: I have a GROUP_CONCAT request that works as intended, except that I would like to make concat a merged response, not a raw ID field.

Current request:

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 

It gives the 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 | +----------+---------+ 

* Table users_roles: *

 +----------+---------+ | user_ID | role_ID | +----------+---------+ | 1 | 1 | +----------+---------+ | 2 | 1 | +----------+---------+ | 2 | 2 | +----------+---------+ | 2 | 3 | +----------+---------+ | 1 | 3 | +----------+---------+ 

Role table

:

 +----------+-----------+ | role_ID | role_name | +----------+-----------+ | 1 | Admin | +----------+-----------+ | 2 | Staff | +----------+-----------+ | 3 | Other | +----------+-----------+ 

Answer 1

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