group_concat on an empty join in MySQL
- by Yossarian
Hello,
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
