Re: Recursive select

Thanks for your answer... it took me a little bit further but ........ I've tried connect by but i can't make it work in my queries (usually with a join or two) since connect by don't like joins..... so i'm a little bit puzzled of how to use connect by....

if i expand your example with a department_id that joins with a tables with the full names of the departments... how do i do that?? Is it possible to do in one query?

Tia
Roland
In article <uyw0OEb695aC0qQ+1fWkbKE0ibLi_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to buurd_at_my-deja.com
> (if that email address didn't require changing)
> On Thu, 18 Nov 1999 15:39:59 GMT, you wrote:
>
> > Hi... i want to ask a question like this:
> >List all ancesters from person p. No only the closest one but ALL
that
> >is in the table. My table looks (somewhat simplified) like this
> >
> >table related(
> > id int,
> > name varchar2(30),
> > parent int
> >)
> >
> >Tia
>
> look at the connect by clause:
>
> 1 select ename, empno, mgr
> 2 from emp
> 3 start with ename = 'SMITH'
> 4* connect by prior mgr = empno
> scott_at_8.0> /
>
> ENAME EMPNO MGR
> ---------- ---------- ----------
> SMITH 7369 7902
> FORD 7902 7566
> JONES 7566 7839
> KING 7839
>
> here SMITH works for FORD works for JONES works for KING. we started
with smith
> and then took smiths MGR field and found the matching EMPNO (ford's
record). it
> walks the tree for us.
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated
June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

--
Roland Carlsson
Certified Java programmer
Skövde
Sweden

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 19 1999 - 08:33:59 CST