Group_concat on an empty connection in MySQL

Question

I have the following problem: I have two tables: (simplified)

+--------+ +-----------+ | User | | Role | +--------+ +-----------+ | ID<PK> | | ID <PK> | +--------+ | Name | +-----------+ 

and the M: N relationship between them

 +-------------+ | User_Role | +-------------+ | User<FK> | | Role<FK> | +-------------+ 

I need to create a view that selects me: User and in one column all its roles (this is done by group_concat).

I tried the following:

 SELECT u.*, group_concat(r.Name separator ',') as Roles FROM User u LEFT JOIN User_Role ur ON ur.User=u.ID LEFT JOIN Role r ON ur.Role=r.ID GROUP BY u.ID; 

However, this works for a user with specific roles. Users without a role are not returned. How can I change the instruction to return to me a user with an empty row in the Roles column when the User does not have any role?

Explanation: I pass the SQL data directly to the grid, which then formats itself, and it is easier for me to create a slow and complex view than formatting it in my code.

I am using MySQL

Answer 1

I tried your SQL and did not have pb to run it, all rows are returned even to a user without a role, the only thing is that you have null in the roles column to avoid using the COALESCE function to get an empty row when the field is NULL.

 SELECT u.*, group_concat(COALESCE(r.Name, "") separator ',') as Roles FROM User u LEFT JOIN User_Role ur ON ur.User=u.ID LEFT JOIN Role r ON ur.Role=r.ID GROUP BY u.ID;