group_concat on an empty join in MySQL

grouping, join, mysql

Solution

I have try your `SQL` and have no pb to run it , all row are returned even user without role, the only thing that you have null into the roles column, to avoid that use `COALESCE` function to get an empty string when the field will be 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;

Problem

I've got the following problem: I have two tables: (simplified) ``` +--------+ +-----------+ | User | | Role | +--------+ +-----------+ | ID<PK> | | ID <PK> | +--------+ | Name | +-----------+ ``` and M:N relationship between them ``` +-------------+ | User_Role | +-------------+ | User<FK> | | Role<FK> | +-------------+ ``` I need to create a view, which selects me: User, and in one column, all of his Roles (this is done by group_concat). I've tried 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 an user with some defined roles. Users without role aren't returned. How can I modify the statement, to return me User with empty string in Roles column when User doesn't have any Role? Explanation: I'm passing the SQL data directly to a grid, which then formats itself, and it is easier for me to create slow and complicated view, than to format it in my code. I'm using MySQL

Original source