Connect by Clause

[KGP]

Hi ,

I want to write a query which gives me tree structure as each node which has children should be displayed with its entire structure.Let me explain ....
1.I have hierarichal data for which I used connet by clause and I am getting tree structure.
2.Now I want to perform task as(in front end):I will have list of all nodes, I will select any one of node and it should show me tree after that node (considering start with clause with that selected node).
e.g.
T1
|_T2
|_T3 and T4
|_T31 |_T41

3.When I select T2,it should show me structure as T2,T3(with children) and T4 (with children)
4.when I select T3 then it should show me T3 and T31.
5.This is achieved with when I write start with = 'T2' but instead of this I want each node with its own tree.I want my query returning output as:
child_id Parent_id Node
T1 -1 T1

T2 T1 T2
T3 T2 T2
T31 T3 T2
T4 T2 T2
T41 T4 T2

T3 T2 T3
T31 T3 T3

T4 T2 T4
T41 T4 T4




Can anyone help in this?

Regards
KGP

[debasisdas]

COLUMN EMPLOYEE FORMAT A20
----------------------------------------------------------------------
SELECT LEVEL,LPAD(' ',2*(LEVEL-1))||ENAME "EMPLOYEE" ,EMPNO,MGR FROM EMP
START WITH MGR IS NULL CONNECT BY PRIOR EMPNO=MGR
------------------------------------------------------------------

try the above query on EMP table of SCOTT schema

n for frontend i should know what is the frontend u r using for the purpose

[KGP]

Hi Debasis,
Thanks for your reply.
But my task is not simple.If you read my first mail: I have already done with tree structure using CONNECT BY clause.
I want tree straing with EACH PARENT node repeat in query output.
e.g.
If node 1 has 2 and 3 as child
node 2 has 21 and 22 as child &
node 3 has 31 and 32 as child then
my regular query will return:
child,parent
1,-1
2,1
21,2
22,2
3,2
31,3
32,3

but i want followingnew rows along with above 7 rows:
21,2
22,2
31,3
32,3

because my where clause will contain node for which I want display the tree and not the whole sincenode -1.

[Mani kansal]

Hi as m new to oracle so could u please tell me some site where i could get
the sql problems.Basically,i need some questions so that i can apply my concepts on that.For practice purpose.hope u would help me on that.

[debasisdas]

Hi please try this code

SELECT LEVEL,LPAD(' ',2*(LEVEL-1))||ENAME "EMPLOYEE", EMPNO,MGR,SAL FROM EMP
CONNECT BY PRIOR mgr=EMPNO
 

try this on previous schema with previously specified column formatting