Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Query but going backwards
Hierarchical Query but going backwards [message #257999] Thu, 09 August 2007 15:06 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Not sure if that makes any sense or not.

Let me try to explain. If I start with a child node, can I go backwards to find out all the parents and root?

Something like:

Child Parent
100 NULL
101 100
102 100
103 100
104 103
105 104
201 NULL
202 201
203 201
204 203
205 204


If my query started with 104 then I would return 103, 100. Or, if I start with 205, then I would return 204, 203, 201.

Instead of going down, like starting with 100 or 200, I want to go back up towards the top.




[Updated on: Thu, 09 August 2007 23:05] by Moderator

Report message to a moderator

Re: Hierarchical Query but going backwards [message #258007 is a reply to message #257999] Thu, 09 August 2007 16:17 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Do you mean like this?

MYDBA@orcl > select level, emp.* from emp connect by empno = prior mgr start with ename = 'SMITH';

     LEVEL      EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
         1       7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
         2       7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                    20
         3       7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
         4       7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000

4 rows selected.

Re: Hierarchical Query but going backwards [message #258008 is a reply to message #258007] Thu, 09 August 2007 16:30 Go to previous message
Duane
Messages: 452
Registered: December 2002
Senior Member
Exactly!

Ah, I see, you are using the "prior" key word to make it work.

That's it, thanks.
Previous Topic: Outer Join Constructs
Next Topic: about image file
Goto Forum:
  


Current Time: Sun Dec 11 08:33:48 CST 2016

Total time taken to generate the page: 0.09639 seconds