sql - how to navigate in self loop tables? -

consider following table

create table employee (   empno    number not null,   ename    varchar2(100),   salary   number,   hiredate date,   manager  number );  alter table employee add constraint pk_emp primary key (empno);  alter table employee     add constraint fk_mgr foreign key (manager)       references employee (empno); 

which self looped table i.e. every employee has manager, except root.

i want run following query on table:

find employees having more salary managers?

---

p.s.

there 1 root in structure

consider following query

select lpad(emp.ename, (level-1)*5 + length(emp.ename), ' ') "hierarchy"     employee emp     start emp.manager null     connect manager = prior empno; 

the result this:

alice     alex     abbey sarah jack     bill     jacob     valencia bob     babak ... 

i made following query

select lpad(emp.ename, (level-1)*5 + length(emp.ename), ' ') "hierarchy"   employee emp     start empno in (select empno employee)     connect prior manager = empno;  

which makes subtree every employee in employee table bottom top, don't know how navigate through desired result!

here 1 way it

with fullemployee (empno, ename, salary, key) (   select a.empno, a.ename, a.salary, a.empno || '.'        employee   a.manager null   union   select c.empno, c.ename, c.salary, d.key || '.' || c.empno        employee c       inner join fullemployee d on c.manager = d.empno ) select e.ename, f.ename manager fullemployee e inner join fullemployee f on e.key f.key || '%' , e.key <> f.key e.salary > f.salary 

or equivalently

with fullemployee (empno, ename, salary, key) (    select empno, ename, salary, sys_connect_by_path(empno, '.') || '.'    employee    start manager null    connect prior empno = manager ) select e.ename, f.ename manager fullemployee e inner join fullemployee f on e.key f.key || '%' , e.key <> f.key e.salary > f.salary 

sql fiddle - http://sqlfiddle.com/#!4/37f4ae/35