Problem

Given a table of users and their roles, write a SQL query to find all the users who are in more than one role.
User table:
+----+-------+
| id | name  |
+----+-------+
| 1  | John  |
| 2  | Jane  |
| 3  | Dave  |
| 4  | Sarah |
+----+-------+
Role table:
+----+---------+
| id | name    |
+----+---------+
| 1  | Admin   |
| 2  | Manager |
| 3  | Analyst |
| 4  | User    |
+----+---------+
UserRole table:
+----+---------+----------+
| id | user_id | role_id  |
+----+---------+----------+
| 1  | 1       | 1        |
| 2  | 1       | 2        |
| 3  | 2       | 2        |
| 4  | 2       | 3        |
| 5  | 3       | 3        |
| 6  | 4       | 4        |
+---- +---------+----------+
Output:
+----+-------+
| id | name  |
+----+-------+
| 1  | John  |
| 2  | Jane  |
| 3  | Dave  |
+----+-------+

Solution

SELECT u.id, u.name
FROM User u
JOIN UserRole ur ON u.id = ur.user_id
GROUP BY u.id, u.name
HAVING COUNT(ur.role_id) > 1;