GROUP_CONCAT() like function to return fields that don't match?

Question

I have three tables in my database, for the purposes of discussion let's say they are:

USERS   
-----
user_id
user_name

ROLES
-----
role_id
role

USER_ROLES
----------
user_role_id
user_id
role_id

I can easily use GROUP_CONCAT() to return a comma separated list of roles the user does have like so:

SELECT u.user_id, u.user_name, GROUP_CONCAT(role) AS roles_held
FROM users u,
     roles r,
     user_roles ur
WHERE u.user_id = ur.user_id
  AND r.role_id = ur.role_id
GROUP BY u.user_id, u.user_name;

What I'd like to do is also return a list of roles the user does not have.

My initial thought was using sub-query then concatenating that, but I can't seem to get that to work. Can someone point me in the right direction?

Thank you!

EDIT: To clarify, the desired output would be a query that returns the user_id, user_name, a concatenated string of the roles the user does have and a concatenated string of the roles the user does not have. So for example:

USER_ID    USER_NAME    ROLES_HELD      ROLES_LACKED
1          Bob          User,Writer     Editor,Admin
2          Doug         User            Writer,Editor,Admin

Answer 1

I agree with using a CROSS JOIN here, but it doesn't need to be quite so complicated:

SELECT
    u.user_id,
    u.user_name,
    GROUP_CONCAT(case when ur.user_id is not null then r.role end) AS roles_held,
    GROUP_CONCAT(case when ur.user_id is null then r.role end) as roles_lacked
FROM
    users u
    CROSS JOIN roles r
    LEFT JOIN user_roles ur ON
        u.user_id = ur.user_id
        AND r.role_id = ur.role_id
GROUP BY
    u.user_id, u.user_name