CODEWITHSUNDEEP
sqljoin
user3578325
user3578325

Reputation: 239

need help in joining 3 views

I want to join 3 views in sql. Here is my code :

select v1.grantee, v1.privilege, granted_role, v3.privilege, table_name 
from dba_sys_privs v1, dba_role_privs v2 , dba_tab_privs v3 
where v1.grantee=v2.grantee and v1.grantee=v3.grantee and v1.grantee='USER1';

My problem is when dba_tab_privs or dba_role_privs do not contain any row which the grantee is user1 the result will be 0 rows, so what i need is to show the result with empty space in granted_role and v3.privilege and table_name columns in that case. I also tried this code :

select utilisateur, Sys_privs, Role, Obj_privs, table_name
from
(select grantee as utilisateur, privilege as Sys_privs from dba_sys_privs where grantee='USER1'),
(select granted_role as Role from dba_role_privs where grantee='USER1'),
(select privilege as Obj_privs, table_name from dba_tab_privs where grantee='USER1');

but unfortunately the result is zero rows too.

Upvotes: 1

Views: 235

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You're using deprecated syntax for joining, update those, and use LEFT JOIN to not exclude records from your result set where they don't exist in all three tables:

SELECT v1.grantee, v1.privilege, granted_role, v3.privilege, table_name 
FROM dba_sys_privs v1
LEFT JOIN dba_role_privs v2 
  ON v1.grantee=v2.grantee
LEFT JOIN dba_tab_privs v3 
  ON v1.grantee=v3.grantee
WHERE v1.grantee='USER1';

Edit: Per spencer7593's comment, your JOIN criteria may be incomplete/illogical if these are Oracle System Catalog views as he mentions.

Upvotes: 1

Related Questions