CODEWITHSUNDEEP
sqloracle-database
Yasar Abdullah
Yasar Abdullah

Reputation: 73

ORACLE SQL compare two or more rows in same table

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.

Upvotes: 4

Views: 2177

Answers (5)

Utsav
Utsav

Reputation: 8121

As @jarlh told, this would do. Here I am not using filter on uname

with tbl (Uname,Grp_ID,Role_ID) as 
(   select 'usr1',10,1032 from dual union all
    select 'usr1',10,1034 from dual union all
    select 'usr1',20,1032 from dual union all
    select 'usr1',20,1034 from dual union all
    select 'usr1',30,1032 from dual union all
    select 'usr1',40,1032 from dual union all
    select 'usr1',50,1034 from dual union all
    select 'usr1',50,1034 from dual union all
    select 'usr1',60,1018 from dual union all
    select 'usr1',70,1057 from dual
)
--Query
select grp_id
 ,count(distinct role_id) as count_dist_role_id 
from tbl 
where Role_ID in (1032,1034)
group by grp_id 
having count(distinct role_id)=2

Output

+--------+--------------------+
| GRP_ID | COUNT_DIST_ROLE_ID |
+--------+--------------------+
|     20 |                  2 |
|     10 |                  2 |
+--------+--------------------+

Upvotes: 0

xQbert
xQbert

Reputation: 35343

Just to offer a different answer:

SELECT GrpID FROM theTable WHERE roleId = 1032
INTERSECT
SELECT GrpID FROM theTable WHERE roleID = 1034

or

SELECT 'Grp_ID '|| GRPID ||' has 1032 and 1034 as Role_IDs' 
FROM theTable 
WHERE roleId = 1032
INTERSECT
SELECT 'Grp_ID '|| GRPID ||' has 1032 and 1034 as Role_IDs' 
FROM theTable 
WHERE roleID = 1034

if you need the full text as in your question...

but I'm not a big fan of adding text like this in the SQL presentation layer should take care of it.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 2

Josh Smeaton
Josh Smeaton

Reputation: 48730

What you need to do is join the table to itself on group id, and then filter for rows that contain both of your role ids. Each table involved in the join contributes a different role. If you needed to filter on 3 values, then you'd need 3 table clauses (two joins).

WITH thetable AS (
  SELECT 10 grpid, 1032 roleid FROM dual UNION ALL
  SELECT 10 grpid, 1034 roleid FROM dual UNION ALL
  SELECT 20 grpid, 1032 roleid FROM dual UNION ALL
  SELECT 20 grpid, 1034 roleid FROM dual UNION ALL
  SELECT 30 grpid, 1032 roleid FROM dual UNION ALL
  SELECT 40 grpid, 1032 roleid FROM dual UNION ALL
  SELECT 50 grpid, 1034 roleid FROM dual UNION ALL
  SELECT 50 grpid, 1034 roleid FROM dual UNION ALL
  SELECT 60 grpid, 1018 roleid FROM dual UNION ALL
  SELECT 70 grpid, 1057 roleid FROM dual
)
SELECT t1.grpid, t1.roleid, t2.roleid
FROM thetable t1
JOIN thetable t2 ON t1.grpid = t2.grpid
WHERE t1.roleid = 1032 AND t2.roleid = 1034;


groupid  roleid  roleid_1
10       1032    1034
20       1032    1034

Upvotes: 1

SandPiper
SandPiper

Reputation: 2906

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

Upvotes: 1

Related Questions