OraFAQ Forum: Training & Certification » how to find hierachy of tables

Home » Other » Training & Certification » how to find hierachy of tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to find hierachy of tables [message #291597]

Sat, 05 January 2008 05:13

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

Michel Cadot
Messages: 68641
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

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

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: how to find hierachy of tables [message #291654 is a reply to message #291634]

Sat, 05 January 2008 12:20

mgangadhar_143
Messages: 3
Registered: January 2008

Junior Member

thanq u for ur replay, pls give me some code.

Report message to a moderator

Re: how to find hierachy of tables [message #291656 is a reply to message #291654]

Sat, 05 January 2008 12:45

Michel Cadot
Messages: 68641
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

Report message to a moderator

Previous Topic:	want a Real application clustering certifications
Next Topic:	D2K interview questions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 08:00:14 CDT 2024