logic in HAVING clause to get multiple values of a group by result

Question

Imagine I have a table with data as below:

ROLE_ID | USER_ID  |  CODE
---------------------------------
 14     | USER A   |   001
 15     | USER A   |   002
 11     | USER B   |   004
 13     | USER D   |   005
 13     | USER A   |   001
 15     | USER B   |   009
 15     | USER D   |   005
 12     | USER C   |   004
 15     | USER C   |   008
 13     | USER D   |   007
 15     | USER D   |   007

I want to get the User ids and codes that only have 13 and 15 role_ids. So based on the data above I would like back the following

USER D |  005
USER D |  007

I have the query below, however, it only brings back one, not both.

   SELECT a.user_id, a.code
   FROM my_table a
   WHERE a.ROLE_ID in (13,15,11,14)
   group by a.USER_ID, a.code
    having sum( case when a.role_id in (13,15) then 1 else 0 end) = 2
    and sum( case when a.role_id in (11,14) then 1 else 0 end) = 0  
   ORDER BY USER_ID

The above query only brings

USER D |  005

rather than

USER D |  005
USER D |  007

Answer 1

Sometimes just listening to your own words in English translates into the easiest to read SQL:

SELECT DISTINCT a.user_id, a.code
   FROM my_table a
   WHERE a.user_id in 
       (SELECT b.user_id
       FROM my_table b
       WHERE b.ROLE_ID = 13)
    AND a.user_id in 
       (SELECT b.user_id
       FROM my_table b
       WHERE b.ROLE_ID = 15)
   AND a.user_id NOT IN 
       (SELECT b.user_id
       FROM my_table b
       WHERE b.ROLE_ID NOT IN (13,15))

Answer 2

I will:

SELECT a.user_id, a.code 
FROM my_table a
GROUP BY a.user_id, a.code 
HAVING sum(case when a.role_id in (13, 15) then 1 else 3 end) = 2

:)

Answer 3

As proven by EthanB, your query is working exactly as you desire. There must be something in your project data that is not represented in your question's fabricated data.

I do endorse a pivot as you have executed in your question, but I would write it as a single SUM expression to reduce the number of iterations over the aggregate data. I certainly do not endorse multiple subqueries on each row of the table (1, 2, 3) ...regardless of whether the optimizer is converting the subqueries to multiple JOINs.

Your pivot conditions:

having sum( case when a.role_id in (13,15) then 1 else 0 end) = 2
   and sum( case when a.role_id in (11,14) then 1 else 0 end) = 0 

My recommendation:

As the aggregate data is being iterated, you can keep a tally (+1) of qualifying rows and jump to a disqualifying outcome (+3) after each evaluation. This way, there is only one pass over the aggregate instead of two.

SELECT USER_ID, CODE
FROM my_table
WHERE ROLE_ID IN (13,15,11,14)
GROUP BY USER_ID, CODE
HAVING SUM(CASE WHEN ROLE_ID IN (13,15) THEN 1
                WHEN ROLE_ID IN (11,14) THEN 3 END) = 2

Another way of expressing what these HAVING clauses are doing is:

Require that the first CASE is satisfied twice and that the second CASE is never satisfied.

Demo Link

Alternatively, the above HAVING clause could be less elegantly written as:

HAVING SUM(CASE ROLE_ID
           WHEN 13 THEN 1
           WHEN 15 THEN 1
           WHEN 11 THEN 3
           WHEN 14 THEN 3
           END) = 2

Disclaimer #1: I don't swim in the [oracle] tag pool, I've not investigated how to execute this with PIVOT.

Disclaimer #2: My above advice assumes that ROLE_IDs are unique in the grouped USER_ID+CODE aggregate data. Fringe cases: (a demo)

1. a given group contains ROLE_ID = 13, ROLE_ID = 13, and ROLE_ID = 15 then of course the SUM will be at least 3 and the group will be disqualified.
2. a given group contains only ROLE_ID = 15 and ROLE_ID = 15 then of course the SUM will be 2 and the group will be unintentionally qualified.

To combat scenarios like these, make three separate MAX conditions.

HAVING MAX(CASE WHEN ROLE_ID = 13 THEN 1 END) = 1
   AND MAX(CASE WHEN ROLE_ID = 15 THEN 1 END) = 1
   AND MAX(CASE WHEN ROLE_ID IN (11,14) THEN 1 END) IS NULL

Demo

Answer 4

SELECT user_id, code FROM my_table
WHERE role_id = 13
INTERSECT
SELECT user_id, code FROM my_table
WHERE role_id = 15