Select all users with one role and one role only - many 2 many relationship

I have a page in my admin area where I want to ban users, but I only want to allow the ban of users with one role, that being user. Here's some sample data

User 1 - Roles (banned)

User 2 - Roles (admin, user)

User 3 - Roles (closed)

User 4 - Roles (user)

Only User 4 should be returned because they're the only user to have the user role and the user role only. I tried writing my own query below, but got stuck with how to exclude users with more than just the user role.

SELECT users.*, COUNT(*) AS role_count
FROM users
JOIN roles_users
 ON users.id = roles_users.user_id
JOIN roles
 ON roles_users.role_id = roles.id
GR开发者_如何学COUP BY users.id

Putting WHERE roles.name = 'user' in there makes role_count = 1

Any help appreciated.

SELECT users.*, COUNT(*) AS role_count, 
       SUM(case roles.name when 'user' then 1 else 0 end) AS IsUser
FROM users
JOIN roles_users
 ON users.id = roles_users.user_id
JOIN roles
 ON roles_users.role_id = roles.id
GROUP BY users.id
HAVING COUNT(*) = 1
AND SUM(case roles.name when 'user' then 1 else 0 end) = 1

SELECT * 
  FROM users
 WHERE EXISTS (
               SELECT * 
                 FROM roles_users
                      JOIN roles 
                         ON roles_users.role_id = roles.id               
                WHERE users.id = roles_users.user_id
                      AND roles.name = 'user'
              )
       AND NOT EXISTS (
                       SELECT * 
                         FROM roles_users
                              JOIN roles 
                                 ON roles_users.role_id = roles.id               
                        WHERE users.id = roles_users.user_id
                              AND roles.name <> 'user'
                      );

One should not put the name in the where clause in the inner query, otherwise it will only be counted the roles wich name is 'user'. First select the users that have only one role, then, among these users, verify those that their roles is the user role.

I believe the following will do that.

SELECT 
    u.id
    , roles.name
FROM
    users u
    INNER JOIN roles_users ON u.id = roles_users.user_id
    INNER JOIN roles       ON roles_users.role_id = roles.id
WHERE
    exists (
        SELECT
            users.id, COUNT(*) AS role_count
        FROM
            users u2
            JOIN roles_users ON u2.id = roles_users.user_id
        WHERE
            u2.id = u.id
        GROUP BY 
            users.id
        HAVING COUNT(*) = 1
           )
    AND roles.name = 'user'