sql - Join on multiple tables where table column has mixed values -

hy guys! last few days i've been searching s solution on web , so. it's first question please patient :)

before explaining apologize if simple, i've tried think of , i've got nothing. so, let's start :)

the problem in t3_access.auth_id there values user_id , group_id, need write query list users listed in t3_access.auth_id column , users behind group in t3_access.auth_id.

is possible distinguish between users connected user_id or group_id in query? more welcome.

here sqlfiddle link: http://sqlfiddle.com/#!2/b6dd7/5

i have 4 tables structure:

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) 

here sample data:

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 t1_users ("user_id", "name", "pwd_opts", "usr_opts") values ('peter','peter pan','ok','restricted'); insert t1_users ("user_id", "name", "pwd_opts", "usr_opts") values ('george','george michael','ok','normal'); insert t1_users ("user_id", "name", "pwd_opts", "usr_opts") values ('danny','danny boy','lock','special'); insert t1_users ("user_id", "name", "pwd_opts", "usr_opts") values ('john','john wayne','ok','normal');   insert t2_connections (user_id, group_id, conn_opts) values('peter','group1','normal'); insert t2_connections (user_id, group_id, conn_opts) values('peter','group2','normal'); insert t2_connections (user_id, group_id, conn_opts) values('george','group2','special'); insert t2_connections (user_id, group_id, conn_opts) values('danny','group2','normal'); insert t2_connections (user_id, group_id, conn_opts) values('john','group3','normal');   insert t3_access(auth_id, class_name, gr_name, access) values('peter','class1','profile1','read'); insert t3_access(auth_id, class_name, gr_name, access) values('group2','class1','profile2','read'); insert t3_access(auth_id, class_name, gr_name, access) values('group3','class3','profile3','update'); insert t3_access(auth_id, class_name, gr_name, access) values('george','class2','profile2','execute'); insert t3_access(auth_id, class_name, gr_name, access) values('john','class4','profile4','none');  insert t4_groups (group_id, "group_name") values ('group1', 'first group'); insert t4_groups (group_id, "group_name") values ('group2', 'second group'); insert t4_groups (group_id, "group_name") values ('group3', 'third group'); 

what i've tried (i've tried union, in, exists between 2 selects) nothing gets me expected result:

select t2_connections.*, t3_access.* t2_connections join t3_access on t2_connections.group_id = t3_access.auth_id  group_id in (select auth_id t3_access auth_id 'group%');  select t2_connections.*, t3_access.* t3_access join t2_connections on t3_access.auth_id = t2_connections.user_id auth_id in (select auth_id t3_access user_id 'george' or auth_id 'danny' or auth_id 'peter' or auth_id 'john' ); 

...and i've tried:

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 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 exists (select * t3_access 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 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 exists (select * t3_access t3_access.auth_id = t2_connections.group_id); 

the expected output should this:

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 

with example setup being modified follows ...

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 t1_users (user_id, name, pwd_opts, usr_opts) values ('peter','peter pan','ok','restricted'); insert t1_users (user_id, name, pwd_opts, usr_opts) values ('george','george michael','ok','normal'); insert t1_users (user_id, name, pwd_opts, usr_opts) values ('danny','danny boy','lock','special'); insert t1_users (user_id, name, pwd_opts, usr_opts) values ('john','john wayne','ok','normal');  insert t2_connections (user_id, group_id, conn_opts) values('peter','group1','normal'); insert t2_connections (user_id, group_id, conn_opts) values('peter','group2','normal'); insert t2_connections (user_id, group_id, conn_opts) values('george','group2','special'); insert t2_connections (user_id, group_id, conn_opts) values('danny','group2','normal'); insert t2_connections (user_id, group_id, conn_opts) values('john','group3','normal');  insert t3_access(auth_id, class_name, gr_name, acces) values('peter','class1','profile1','read'); insert t3_access(auth_id, class_name, gr_name, acces) values('group2','class1','profile2','read'); insert t3_access(auth_id, class_name, gr_name, acces) values('group3','class3','profile3','update'); insert t3_access(auth_id, class_name, gr_name, acces) values('george','class2','profile2','execute'); insert t3_access(auth_id, class_name, gr_name, acces) values('john','class4','profile4','none');  insert t4_groups (group_id, group_name) values ('group1', 'first group'); insert t4_groups (group_id, group_name) values ('group2', 'second group'); insert t4_groups (group_id, group_name) values ('group3', 'third group');  commit; 

the closest need be:

with all_users$ (     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     t3_access         join t1_users u on u.user_id = a.auth_id         join t2_connections c on c.user_id = a.auth_id ), all_groups$ (     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     t3_access         join t2_connections c on c.group_id = a.auth_id         join t1_users u on u.user_id = c.user_id     exists (             select 1             t4_groups g             g.group_id = a.auth_id         ) ) select * all_users$ au not exists (         select 1         all_groups$ gx         gx.user_id = au.user_id             , gx.group_id = au.group_id             , gx.class_name = au.class_name     ) -- union -- select * all_groups$ ag ;