Блог

Привет, ребята! Последние несколько дней я искал решение s в Интернете и ТАК ДАЛЕЕ. Это мой первый вопрос, поэтому, пожалуйста, наберитесь терпения

Перед объяснением я хотел бы извиниться, если это слишком просто, но я перепробовал все, что мог придумать, и у меня ничего не получилось. Итак, давайте начнем

Проблема в том, что в t3_access.auth_id есть такие значения, как user_id и group_id, поэтому мне нужно написать запрос, в котором будут перечислены все пользователи, перечисленные в столбце t3_access.auth_id, а также пользователи, которые находятся за группой в t3_access.auth_id.

Возможно ли вообще различать пользователей, которые подключались с помощью user_id или group_id в каком-либо запросе? ЛЮБАЯ помощь более чем приветствуется.

Вот ссылка на sqlfiddle: http://sqlfiddle.com /#!2/b6dd7/5

У меня есть четыре таблицы с их структурой:

 t1_users (user_id, name, pwd_opts)
t2_connections (user_id, group_id, conn_opts)
t3_access (auth_id, class_name, gr_name)
t4_groups (group_id, group_name)
  

Вот пример данных:

 create table t1_users ("user_id" varchar2(10), "name" varchar2(10), "pwd_opts" varchar2(10));
create table t2_connections ("user_id" varchar2(10), "group_id" varchar2(10), "conn_opts" varchar2(10));
create table t3_access ("auth_id" varchar2(10), "class_name" varchar2(10), "gr_name" varchar2(10), "access" varchar2(10));

create table t4_groups ("GROUP_ID" varchar2(10), "group_name" varchar2(20));

insert into t1_users ("user_id", "name", "pwd_opts", "usr_opts") values ('Peter','Peter Pan','OK','RESTRICTED');
insert into t1_users ("user_id", "name", "pwd_opts", "usr_opts") values ('George','George Michael','OK','NORMAL');
insert into t1_users ("user_id", "name", "pwd_opts", "usr_opts") values ('Danny','Danny Boy','LOCK','SPECIAL');
insert into t1_users ("user_id", "name", "pwd_opts", "usr_opts") values ('John','John Wayne','OK','NORMAL');


insert into t2_connections (user_id, group_id, conn_opts) values('Peter','GROUP1','NORMAL');
insert into t2_connections (user_id, group_id, conn_opts) values('Peter','GROUP2','NORMAL');
insert into t2_connections (user_id, group_id, conn_opts) values('George','GROUP2','SPECIAL');
insert into t2_connections (user_id, group_id, conn_opts) values('Danny','GROUP2','NORMAL');
insert into t2_connections (user_id, group_id, conn_opts) values('John','GROUP3','NORMAL');


insert into t3_access(auth_id, class_name, gr_name, access) values('Peter','class1','PROFILE1','READ');
insert into t3_access(auth_id, class_name, gr_name, access) values('GROUP2','class1','PROFILE2','READ');
insert into t3_access(auth_id, class_name, gr_name, access) values('GROUP3','class3','PROFILE3','UPDATE');
insert into t3_access(auth_id, class_name, gr_name, access) values('George','class2','PROFILE2','EXECUTE');
insert into t3_access(auth_id, class_name, gr_name, access) values('John','class4','PROFILE4','NONE');

insert into t4_groups (group_id, "group_name") VALUES ('GROUP1', 'first group');
insert into t4_groups (group_id, "group_name") VALUES ('GROUP2', 'second group');
insert into t4_groups (group_id, "group_name") VALUES ('GROUP3', 'third group');
  

То, что я пробовал (я пробовал UNION, IN, СУЩЕСТВУЕТ между двумя выборками), но ничего не дает мне ожидаемого результата:

 select t2_connections.*, t3_access.*
from t2_connections
join t3_access ON t2_connections.group_id = t3_access.auth_id 
where group_id IN (select auth_id from t3_access where auth_id like 'GROUP%');

select t2_connections.*, t3_access.*
from t3_access
join t2_connections ON t3_access.auth_id = t2_connections.user_id
where auth_id IN (select auth_id from t3_access where user_id like 'George' or auth_id like 'Danny' or auth_id like 'Peter' or auth_id like 'John' );
  

… а также я пробовал:

 select t1_users.user_id, t1_users.name, t1_users.pwd_opts, t1_users.usr_opts,
       t2_connections.group_id, t2_connections.conn_opts, t3_access.class_name, t3_access.gr_name,
       t3_access.access
from t2_connections
join t1_users on t2_connections.user_id = t1_users.user_id
join t4_groups on t2_connections.group_id = t4_groups.group_id
join t3_access on t2_connections.group_id = t3_access.auth_id
where exists (select * from t3_access where t3_access.auth_id = t2_connections.user_id);
union 
select t1_users.user_id, t1_users.name, t1_users.pwd_opts, t1_users.usr_opts,
       t2_connections.group_id, t2_connections.conn_opts, t3_access.class_name, t3_access.gr_name,
       t3_access.access
from t2_connections
join t1_users on t2_connections.user_id = t1_users.user_id
join t4_groups on t2_connections.group_id = t4_groups.group_id
join t3_access on t2_connections.group_id = t3_access.auth_id
where exists (select * from t3_access where t3_access.auth_id = t2_connections.group_id);
  

Ожидаемый результат должен выглядеть примерно так:

 user_id|name |pwd_|usr_opts    |group_id|conn_opt|cl_name |gr_name   |access
Peter | bla1 | ok | restricted | group1 | normal | class1 | profile1 | read
Peter | bla1 | ok | restricted | group2 | normal | class1 | profile2 | read
George| bla3 | ok | normal     | group2 | special| class2 | profile2 | execute
George| bla3 | ok | normal     | group2 | special| class1 | profile2 | read
Danny | bla4 |lock| special    | group2 | normal | class1 | profile2 | read
John  | bla5 | ok | normal     | group2 | normal | class4 | profile4 | none
John  | bla5 | ok | noraml     | group3 | normal | class3 | profile3 | update
  

При этом настройка вашего примера изменяется следующим образом…

 create table t1_users (user_id varchar2(10), name varchar2(20), pwd_opts varchar2(10), usr_opts varchar2(15));
create table t2_connections (user_id varchar2(10), group_id varchar2(10), conn_opts varchar2(10));
create table t3_access (auth_id varchar2(10), class_name varchar2(10), gr_name varchar2(10), acces varchar2(10));
create table t4_groups (group_id varchar2(10), group_name varchar2(20));

insert into t1_users (user_id, name, pwd_opts, usr_opts) values ('Peter','Peter Pan','OK','RESTRICTED');
insert into t1_users (user_id, name, pwd_opts, usr_opts) values ('George','George Michael','OK','NORMAL');
insert into t1_users (user_id, name, pwd_opts, usr_opts) values ('Danny','Danny Boy','LOCK','SPECIAL');
insert into t1_users (user_id, name, pwd_opts, usr_opts) values ('John','John Wayne','OK','NORMAL');

insert into t2_connections (user_id, group_id, conn_opts) values('Peter','GROUP1','NORMAL');
insert into t2_connections (user_id, group_id, conn_opts) values('Peter','GROUP2','NORMAL');
insert into t2_connections (user_id, group_id, conn_opts) values('George','GROUP2','SPECIAL');
insert into t2_connections (user_id, group_id, conn_opts) values('Danny','GROUP2','NORMAL');
insert into t2_connections (user_id, group_id, conn_opts) values('John','GROUP3','NORMAL');

insert into t3_access(auth_id, class_name, gr_name, acces) values('Peter','class1','PROFILE1','READ');
insert into t3_access(auth_id, class_name, gr_name, acces) values('GROUP2','class1','PROFILE2','READ');
insert into t3_access(auth_id, class_name, gr_name, acces) values('GROUP3','class3','PROFILE3','UPDATE');
insert into t3_access(auth_id, class_name, gr_name, acces) values('George','class2','PROFILE2','EXECUTE');
insert into t3_access(auth_id, class_name, gr_name, acces) values('John','class4','PROFILE4','NONE');

insert into t4_groups (group_id, group_name) VALUES ('GROUP1', 'first group');
insert into t4_groups (group_id, group_name) VALUES ('GROUP2', 'second group');
insert into t4_groups (group_id, group_name) VALUES ('GROUP3', 'third group');

commit;
  

Самое близкое, что я мог бы получить от того, что вам нужно, было бы:

 with all_users$ as (
    select
        U.user_id, U.name, U.pwd_opts, U.usr_opts, C.group_id, C.conn_opts, A.class_name, A.gr_name, A.acces
    from t3_access A
        join t1_users U on U.user_id = A.auth_id
        join t2_connections C on C.user_id = A.auth_id
),
all_groups$ as (
    select
        U.user_id, U.name, U.pwd_opts, U.usr_opts, C.group_id, C.conn_opts, A.class_name, A.gr_name, A.acces
    from t3_access A
        join t2_connections C on C.group_id = A.auth_id
        join t1_users U on U.user_id = C.user_id
    where exists (
            select 1
            from t4_groups G
            where G.group_id = A.auth_id
        )
)
select *
from all_users$ AU
where not exists (
        select 1
        from all_groups$ Gx
        where Gx.user_id = AU.user_id
            and Gx.group_id = AU.group_id
            and Gx.class_name = AU.class_name
    )
--
union all
--
select *
from all_groups$ AG
;