0

I have the following query to find employees who are not managers in the EMP table of oracle

 select * from emp e1
 where not exists (select null from emp e2 
                    where e2.mgr=e1.empno)

I need output using start with connect by clause , thus avoiding self join

Lara
  • 49
  • 6
  • `connect by` is for hierarchical queries. For instance, we might use it to display a family connected as Grandparent, Parent, Child. What is the hierarchy in this requirement? – APC Jun 14 '15 at 14:15
  • Ok let me make it simple, I have – Lara Jun 14 '15 at 14:25
  • select empno,mgr,ename||' works for '|| prior ename "Employees and their Managers" From emp where mgr is not null start with mgr is null connect by prior empno = mgr – Lara Jun 14 '15 at 14:25
  • I just want to have output like "Employee Turner is not a manager" – Lara Jun 14 '15 at 14:27

1 Answers1

1

There is a function, CONNECT_BY_ISLEAF(), which indicates whether a given row in a hierarchical query is a leaf node. In the EMP table, employees who are not managers will be leaf nodes.

So, we can use this function in a nested hierarchical query to filter the non-managers:

select empno, mgr, ename 
from (
    select empno, mgr, ename, CONNECT_BY_ISLEAF cbi
    from emp 
    start with mgr is null 
    connect by prior empno = mgr
) where cbi = 1
/

Oracle has several neat functions for interrogating hierarchies. Find out more.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Wow as always you guys rock!! – Lara Jun 14 '15 at 17:07
  • @Lara - if this answer has helped you, [please accept it](http://stackoverflow.com/help/someone-answers). This helps future seekers who come looking for answers. – APC Jun 14 '15 at 17:34