How to return ids with specific values in another column I have a table USER_ROLE with the format like this.

+--------+---------+
|USER_ID | ROLE    |
+--------+---------+
|1       | ADD     |
+--------+---------+
|1       | DELETE  |
+--------+---------+
|1       | VIEW    |
+--------+---------+
|1       | TRANSFER|
+--------+---------+
|2       | ADD     |
+--------+---------+
|2       | DELETE  |
+--------+---------+
|2       | VIEW    |
+--------+---------+
|2       | TRANSFER|
+--------+---------+
|3       | ADD     |
+--------+---------+
|3       | DELETE  |
+--------+---------+
|4       | VIEW    |
+--------+---------+
|4       | TRANSFER|
+--------+---------+


I want to find the USER_IDs that have all the below ROLEs.


ADD 
DELETE  
VIEW


So it should return 1 and 2 from above example.

I managed to get the results with below queries.

SELECT DISTINCT USER_ID 
FROM USER_ROLE r1
WHERE EXISTS (SELECT * FROM USER_ROLE r2 WHERE r2.ROLE = 'ADD' AND r1.USER_ID = r2.USER_ID)
AND EXISTS (SELECT * FROM USER_ROLE r2 WHERE r2.ROLE = 'DELETE' AND r1.USER_ID = r2.USER_ID)
AND EXISTS (SELECT * FROM USER_ROLE r2 WHERE r2.ROLE = 'VIEW' AND r1.USER_ID = r2.USER_ID)


and

SELECT USER_ID FROM USER_ROLE WHERE ROLE = 'ADD'
INTERSECT SELECT USER_ID FROM USER_ROLE WHERE ROLE = 'DELETE'
INTERSECT SELECT USER_ID FROM USER_ROLE WHERE ROLE = 'VIEW'


Can you suggest any simple alternatives to these.
SELECT user_id
FROM
user_role
WHERE role IN('ADD','DELETE','VIEW') 
GROUP BY user_id
HAVING count(*)=3


Here is the link to SqlFiddle