Home » SQL & PL/SQL » SQL & PL/SQL » relationship between the tables
relationship between the tables [message #446021] Fri, 05 March 2010 03:17 Go to next message
ind9
Messages: 65
Registered: January 2009
Member
Hi all,
i'm trying to display the heirarichal relationship between the tables (parents-child-subchild).


[b]table structure[/b]
DEPT
 |PK-DEPT_ID
 |
EMP
 |pk-EMP_ID
 |fK emp_DEPT_fK((FK column is dept_id references dept table dept_id column))
 |
 |
EMP_AUTHORIZATION
 |pk-EMP_ID,aUTHORIZTION_ID
 |fk-EMP_AUTH_FK(FK column is EMP_ID references emp table emp_id column)
 | 
 |
 |
EMP_AUTHRIZATION_CARD
 |pk-EMP_ID,aUTHORIZTION_ID and card_id
 |fk-EMP_card_FK(FK column is EMP_ID,aUTHORIZTION_ID references EMP_AUTHORIZATION table EMP_ID,aUTHORIZTION_ID column)
 | 
 |
 |
EMP_AUTHRIZATION_DUP
 |
 |fk-EMP_card_FK(FK column is EMP_ID,aUTHORIZTION_ID,card_id references  EMP_AUTHRIZATION_CARD table EMP_ID,aUTHORIZTION_ID and card_id column)
 
Expected output
...................
table_name                  path
DEPT                        DEPT
EMP                         DEPT/EMP
EMP_AUTHORIZATION           DEPT/EMP/EMP_AUTHORIZATION
EMP_AUTHRIZATION_CARD       DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD
EMP_AUTHRIZATION_DUP        DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD/EMP_AUTHRIZATION_DUP


but by using below query i am not getting complete heirarichy.

SELECT    LEVEL, 
           Table_Name,
           Constraint_Name,
           R_Constraint_Name ,
           SYS_CONNECT_BY_PATH(Table_Name, '/') Path
      FROM all_constraints
      Where Table_Name in ('DEPT','EMP','EMP_AUTHORIZATION','EMP_AUTHRIZATION_CARD','EMP_AUTHRIZATION_DUP') 
         AND owner = 'SCOTT'   
CONNECT BY PRIOR   Constraint_Name = R_Constraint_Name
 order by Path


could you please suggest where i'm going wrong in this.
Re: relationship between the tables [message #446023 is a reply to message #446021] Fri, 05 March 2010 03:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And what's wrong with your other thread on the same subject?
Re: relationship between the tables [message #446025 is a reply to message #446023] Fri, 05 March 2010 03:24 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Exactlty what I was thinking.

Previous Topic: call to function in SYS schema
Next Topic: Migrate Oracle DB to Ms/Sql server
Goto Forum:
  


Current Time: Wed Sep 28 00:38:49 CDT 2016

Total time taken to generate the page: 0.08956 seconds