...

...

I have 2 tables Table1 and Table2 as shown above. My requirement is to get the User_ID from the table2 which has all the roles from a group. Additionally, only those groups need to be checked which has at least 2 roles. If a group_ID has only 1 role then it should not be considered

For example, this is how the result will look like from above 2 tables

user1 has both the roles from group 1 (A,B) -> therefore it is in the results.

user3 has both the roles from group 2 (A,D) -> therefore it is in the results.

user5 has all the roles from group 1(A,B), 2(A,D) and 3(A,B,C) -> therefore it is in the results.

User2 has role C and D which is not a group, hence not shown in the result

User4 has role C which is a group (Group_ID = 4), but the group should have at least 2 roles, hence not shown in the result

....

Select Table2.USER_ID,Table1.GROUP_ID from Table2, Table1 Where Table2.ROLE = Table1.ROLE group by Table1.GROUP_ID,Table2.USER_ID

With the above query, I am able to get the records with user_id assigned any of the role. However, I want to get the User_ID from the table2 which has all the roles from a group.

Any help is much appreciated. I will also make sure to accept the answer leading me to the solution

CodePudding user response：

Here is a solution that will work in Oracle Database; adapt it for SQL Server (if that is possible; I don't know that dialect).

Test data (others may use this too):

create table table1(id number, role varchar2(10), group_id number);

insert into table1 (id, role, group_id)
    select 1, 'A', 1 from dual union all
    select 2, 'B', 1 from dual union all
    select 3, 'A', 2 from dual union all
    select 4, 'D', 2 from dual union all
    select 5, 'A', 3 from dual union all
    select 6, 'B', 3 from dual union all
    select 7, 'C', 3 from dual
;

create table table2 (user_id varchar2(20), role varchar2(10));

insert into table2 (user_id, role)
    select 'user1', 'A' from dual union all
    select 'user1', 'B' from dual union all
    select 'user2', 'C' from dual union all
    select 'user2', 'D' from dual union all
    select 'user3', 'A' from dual union all
    select 'user3', 'D' from dual union all
    select 'user4', 'C' from dual union all
    select 'user5', 'A' from dual union all
    select 'user5', 'B' from dual union all
    select 'user5', 'C' from dual union all
    select 'user5', 'D' from dual
;

commit;

Create user-defined data type (collection of strings representing roles):

create or replace type role_list as table of varchar2(10);
/

Query and output:

select a2.user_id, a1.group_id
from   (
         select user_id, cast(collect(role) as role_list) as user_roles
         from   table2
         group  by user_id
       ) a2
       inner join
       (
         select group_id, cast(collect(role) as role_list) as group_roles
         from   table1
         group  by group_id
       ) a1
       on a1.group_roles submultiset of a2.user_roles
order  by user_id, group_id
;

USER_ID         GROUP_ID
------------  ----------
user1                  1
user3                  2
user5                  1
user5                  2
user5                  3

The strategy is pretty obvious, and should be easy to read directly from the code. Group the roles by group_id in the first table and by user_id in the second. Identify all the pairs (user, group) where all roles for the group are found in the role list of the user - that is exactly what the submultiset comparison operator does.

A more rudimentary query (harder to follow and maintain, and likely slower), but perhaps helpful as it is likely to work with very few changes - if any - in pretty much all SQL dialects, might look like this. Assuming role can't be null in table2 (to make the query slightly simpler):

select q2.user_id, q1.group_id
from   (select distinct user_id  from table2) q2
       cross join
       (select distinct group_id from table1) q1
where  not exists 
       (
         select role
         from   table1
         where  group_id = q1.group_id
           and  role not in
                     (
                       select role
                       from   table2
                       where  user_id = q2.user_id
                     )
       )
order  by user_id, group_id
;