Re: Pure SQL transitive closure with LEVEL

Here's what I got so far, after hours and hours of head-scratching:

SQL> select e1.empno "Manager",e2.empno "Employee" from emp e1,emp e2   2 where e1.empno <> e2.empno
  3 and exists (select 1 from emp e3
  4 where e3.empno = e2.empno
  5 start with e3.mgr = e1.empno
  6 connect by e3.mgr = prior e3.empno)   7 order by e1.empno;

   Manager Employee
---------- ----------

      7566       7369
      7566       7788
      7566       7876
      7566       7902
      7698       7499
      7698       7521
      7698       7844
      7698       7900
      7698       7654
      7782       7934
      7788       7876
      7839       7369
      7839       7654
      7839       7844
      7839       7934
      7839       7902
      7839       7900
      7839       7876
      7839       7788
      7839       7782
      7839       7698
      7839       7566
      7839       7521
      7839       7499
      7902       7369

25 rows selected.

I can't have yet the number of levels of distance between the employees and the managers. I'm still working on it.

Daniel Received on Sun Dec 01 2002 - 17:33:40 CET