Home » Other » Training & Certification » how to find hierachy of tables
how to find hierachy of tables [message #291597] Sat, 05 January 2008 05:13 Go to next message
mgangadhar_143
Messages: 3
Registered: January 2008
Junior Member
hi,friends

how to find hierarchy(child..parent..gradparent..)of tables
using USER_CONSTRAINTS table.

FOR EX WHO IS MANGER TO WHOM WE WRITE
SQL> set pagesize 100
SQL> select level,lpad('  ',2*(level-1))||ename as name from
  2  emp
  3  start with  ename='KING'
  4  connect by prior empno=mgr
  5  /

     LEVEL NAME
---------- -------------------------
         1 KING
         2   JONES
         3     SCOTT
         4       ADAMS
         3     FORD
         4       SMITH
         2   BLAKE
         3     ALLEN
         3     WARD
         3     MARTIN
         3     TURNER
         3     JAMES
         2   CLARK
         3     MILLER

14 rows selected.

i want like this type of output.

[Updated on: Sat, 05 January 2008 06:51]

Report message to a moderator

Re: how to find hierachy of tables [message #291613 is a reply to message #291597] Sat, 05 January 2008 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you already tried and why you can't achieve it.

Regards
Michel

[Updated on: Sat, 05 January 2008 07:40]

Report message to a moderator

Re: how to find hierachy of tables [message #291616 is a reply to message #291613] Sat, 05 January 2008 07:51 Go to previous messageGo to next message
mgangadhar_143
Messages: 3
Registered: January 2008
Junior Member
1 select distinct(lpad(' ',2*(level-1))||table_name) as tablename,level
2 from user_constraints
3 start with table_name='EMPLOYEES'
4 connect by prior r_constraint_name=constraint_name
5* order by level asc
SQL> /

TABLENAME LEVEL
--------------- ----------
EMPLOYEES 1
DEPARTMENTS 2
EMPLOYEES 2
JOBS 2


this query not giving parent tables of departments,jobs and their grand parents.pls give me the solution.

[Updated on: Sat, 05 January 2008 08:25]

Report message to a moderator

Re: how to find hierachy of tables [message #291634 is a reply to message #291616] Sat, 05 January 2008 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey! you formatted your first post, why don't you format the next ones?

Quote:

this query not giving parent tables of departments,jobs and their grand parents.

This is because there is not a hierarchy in the same way as employee -> manager/employee -> manager/employee -> manager.
Here the relation is FK -> PK, search table FK -> PK, search table FK -> PK.
Or in another words, if a manager can be an employee that have a manager, a PK is never a FK. You have to create a (inline) view that simulate a hierarchy.

Beware of loops (for instance, employee references employee).

Regards
Michel
Re: how to find hierachy of tables [message #291654 is a reply to message #291634] Sat, 05 January 2008 12:20 Go to previous messageGo to next message
mgangadhar_143
Messages: 3
Registered: January 2008
Junior Member
thanq u for ur replay, pls give me some code.
Re: how to find hierachy of tables [message #291656 is a reply to message #291654] Sat, 05 January 2008 12:45 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to do it with the elements I gave.
First try to get a select that includes the parent/child relation. Don't try in this step to have the full hierarchy.

Regards
Michel
Previous Topic: want a Real application clustering certifications
Next Topic: D2K interview questions
Goto Forum:
  


Current Time: Sat Dec 10 20:52:56 CST 2016

Total time taken to generate the page: 0.27387 seconds