ORACLE SQL compare two or more rows in same table

Question

I have a table with values similar to this

Uname | Grp_ID | Role_ID
---------------------
usr1  | 10     | 1032
usr1  | 10     | 1034
usr1  | 20     | 1032
usr1  | 20     | 1034
usr1  | 30     | 1032
usr1  | 40     | 1032
usr1  | 50     | 1034
usr1  | 50     | 1034
usr1  | 60     | 1018
usr1  | 70     | 1057

I want output Grp_IDs which have 1032 and 1034 (both) as the Role_IDs For example,

Grp_ID 10 has 1032 and 1034 as ROle_IDs
Grp_ID 20 has 1032 and 1034 as ROle_IDs

Grp_ID 30, 40, 50, 60 ,70 do not have both 1032 and 1034 as Role_IDs

I have tried used inner joins and 'having' but don't seem to get what I want.

Answer 1

I like to approach these problems using group by and having. In this case:

select grp_id
from tbl 
where role_id in (1032, 1034)
group by grp_id 
having count(distinct role_id) = 2;

I find that this method generalizes to many variations of set-within-sets questions.

Answer 2

Select t1.uname, t2.grp_id, t1.role_id from table_name t1 
left join table_name t2 on t1.grp_is = t2.grp_id
Where t1.role_id = 1032 and t2.role_id = 1034