Inner queries on a single table with IN and NOT IN conditions

Question

This is a modification to my previously answered question

I have data in the table like below:

ROLE_ID | USER_ID
------------------
 14     | USER A
 15     | USER A
 11     | USER B
 13     | USER D
 13     | USER A
 15     | USER B
 15     | USER D
 12     | USER C
 15     | USER C

I would like to get user ids that ONLY have 13 and 15. So based on the example above, I should only get back USER D

The query below was provided in my previous answer and the NOT IN part was added by me, however, that doesn't achieve the goal..

select user_id
  from my_table
 where role_id in (13,15) AND role_id not in (11,14)
 group by user_id.
having count(distinct role_id) = 2

Answer 1

Assuming the combination of user_id and role_id is unique, you could do something like

select user_id
  from my_table
 where role_id in (13,15,11,14) 
 group by user_id.
having sum( case when role_id in (13,15) then 1 else 0 end) = 2
   and sum( case when role_id in (11,14) then 1 else 0 end) = 0

If the combination of user_id and role_id is not unique, then the distinct in your original count is necessary and things get a bit more challenging.