| 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 #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: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #291656 is a reply to message #291654] |
Sat, 05 January 2008 12:45 |
|
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
