2

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.

4
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

  • You could put the link to the fiddle in the main body of the answer! – Vérace Jan 23 at 3:45
2

Here is another way to do it, but your INTERSECT method is probably the simplest. You could also do a proper Pivot.

SELECT USER_ID
FROM (
SELECT USER_ID, 
       [ADD] = MAX(CASE WHEN ROLE = 'ADD' THEN 1 ELSE 0 END),
       [DELETE] = MAX(CASE WHEN ROLE = 'DELETE' THEN 1 ELSE 0 END),
       [VIEW] = MAX(CASE WHEN ROLE = 'VIEW' THEN 1 ELSE 0 END)
FROM   USER_ROLE AS r1
GROUP  BY USER_ID 
) AS U
WHERE [ADD] = 1 and [DELETE] = 1 and [VIEW] = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.