CODEWITHSUNDEEP
sqloracle-database
Anthony
Anthony

Reputation: 35928

Inner queries on a single table

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

Upvotes: 1

Views: 113

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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.

Upvotes: 0

Vishal Bardoloi
Vishal Bardoloi

Reputation: 652

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

Upvotes: 0

mtariq
mtariq

Reputation: 410

Try this

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

Upvotes: 3

Ben
Ben

Reputation: 52853

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

Upvotes: 7

Related Questions