Inner queries on a single table

Question

I have a table in my Oracle DB with data stored such as

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

I want to get all the USER_IDs which have ROLE_ID of both 13 and 15. Based on the example above, I should only get back USER_A.

If I write the query below

select * from table where ROLE_ID in (13,15); then I get other users as well.

How can I modify the query so that I only get back USER A

score 7 · Accepted Answer · answered Aug 28 '12 at 19:42

7

You're looking for having...

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

You ensure, by using the distinct, that if there is say two USER A's that you still get one where it has both role_ids

answered Aug 28 '12 at 19:42

Ben

51,770
36
127
149

This works great! thanks! but is there a way to have a NOT IN condition as well? For example, based on the example above, what if i want to put a condition of returning only the ids that have 13, 15 but don't have 14. Simply adding `AND role_id not in (14)` doesn't work.. – Anthony Aug 29 '12 at 00:07
I've asked a separate question on this. http://stackoverflow.com/questions/12169409/inner-queries-on-a-single-table-with-in-and-not-in-conditions – Anthony Aug 29 '12 at 00:35

score 3 · Answer 2 · answered Aug 28 '12 at 19:43

3

Try this

SELECT USER_ID FROM table WHERE ROLE_ID=13
        INTERSECT
SELECT USER_ID FROM table WHERE ROLE_ID=15

answered Aug 28 '12 at 19:43

mtariq

400
1
10

score 0 · Answer 3 · answered Aug 28 '12 at 20:07

0

You can do this with an inner join:

SELECT DISTINCT t1.USER_ID
FROM table t1
JOIN table t2 
 ON t2.USER_ID = t1.USER_ID 
 AND t2.ROLE_ID=15
WHERE t1.ROLE_ID=13

answered Aug 28 '12 at 20:07

Vishal Bardoloi

652
5
18

score 0 · Answer 4 · answered Aug 28 '12 at 20:16

I would do this in the HAVNIG clause only:

select user_id
from t
group by user_id
having max(case when role_id = 13 then 1 else 0 end) = 1 and
       max(case when role_id = 15 then 1 else 0 end) = 1

I find that phrasing these conditions in the HAVING clause provides the most flexibility. You can have "and" conditions, "or" conditions, limit the results to only users with 13 and 15, or whatever using the same basic construct.

Inner queries on a single table

4 Answers4

Linked