| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Recursive SQL
On Thu, 10 Jun 1999 15:39:14 GMT, radioaktiv_at_my-deja.com wrote:
>Looks like a homework assignment to me, so i'll give you a hint...
>Chris wrote:
>
>>Look at the START WITH and CONNECT BY clauses for an Oracle select
>>statement.
>
>>hope this helps.
>
>Thank you, _but_
>
>1st: It does not work with START WITH because I do not know the
>starting point (there are several starting points). If omit this
>clause, Oracle uses all rows in the table as root rows but I do not
>want that.
But you do know the starting point.
> root, but I don't know the starting points, I only have a given
> Person_ID.
It is the set of all projects for which the person is related to. So you start with could be
start with project_id in ( select project_id
from project_pers
where person_id = <the_known_person_id> )
>2nd: START WITH and CONNECT BY are parsing the tree from the root o the
>top, I want to do it viceversa.
START WITH / CONNECT BY can walk up or down the tree. It depends where the PRIOR is placed.
>
>So I still have the same problem (and it is no homework assignment :)
Well, if its really not homework, then I guess I could give you the answer, but you better not be lying to me! I don't want your teacher coming after me :-)
Your initial question was:
> Now I want to see all projects in which a person cooperates and the
> projects to which it is related to and the projects to which them are
> related to and so forth...
> That means I want to go down the tree from one or more leafs to the
> root, but I don't know the starting points, I only have a given
> Person_ID.
So you want something like...
select lpad(’ ’,2*(level-1)) || project_name
from project
start with project_id in ( select project_id
from project_pers
where person_id = <the_known_person_id> )
connect by project_id = prior related_to_project
/
If you want to walk the tree in the other direction, just put the prior on the other side of the equal sign.
connect by prior project_id = related_to_project
hope this helps.
chris.
ps. Make sure you give me credit when you submit this as homework :-)
>
>Knut
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |