CODEWITHSUNDEEP
oracle-databasehierarchical
rkbom9
rkbom9

Reputation: 913

Hierarchical Query CONNECT BY Oracle

I am trying to implement the Connect By query in oracle. Trying to understand how it works. So I have a simple table which looks likes:

empno ename mno
1      KS    null
2      AB     2
3      BC     1
4      TR     3
5      QE     2
6      PL     3
7      LK     6

The Query 

SELECT empno, ename, mno
   FROM test
START WITH ename = 'LK'
   CONNECT BY PRIOR empno = mno;

So when the name is LK I should get the following parent/child rows LK>PL>BC>KS. The SQLFIDDLE But I am not getting the correct results. What I am doing wrong ?

Upvotes: 0

Views: 158

Answers (1)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

No, you should not.

LK's parent is PL. PL's parent is BC. BC's parent is KS. KS has no parent. When you're trying to start tree from LK, you get all it's children - none, because there are no records with mno = 7.

You muddled parent-child order in your query. If you wish to unwind the three from leaf to root, you should ask

SELECT empno, ename, mno
   FROM test
START WITH ename = 'LK'
   CONNECT BY empno = PRIOR mno;

If you wish to see the tree into the natural order, you should to ask

SELECT empno, ename, mno
   FROM test
START WITH mno is null
   CONNECT BY PRIOR empno = mno;

Upvotes: 2

Related Questions