how to navigate in self loop tables?

Question

Consider the 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 is a self looped table i.e. every employee has a manager, except for the root.

I want to run the following query on this table:

find all the employees having more salary than their managers?

P.S.

There is only one root in the structure

consider the following query

SELECT LPAD(emp.ename, (LEVEL-1)*5 + LENGTH(emp.ename), ' ') AS "Hierarchy"
    FROM employee emp
    START WITH emp.manager IS NULL
    CONNECT BY manager = PRIOR empno;

the result would be something like this:

Alice
    Alex
    Abbey
Sarah
Jack
    Bill
    Jacob
    Valencia
Bob
    Babak
...

I made the following query

SELECT LPAD(emp.ename, (LEVEL-1)*5 + LENGTH(emp.ename), ' ') AS "Hierarchy"
  FROM employee emp
    START WITH empno IN (SELECT empno FROM employee)
    CONNECT BY PRIOR manager = empno;

which makes a subtree for every employee in the employee table from bottom to top, but I don't know how to navigate through to get to the desired result!

can a manager have a manager on top of him? And the next one after that, etc? What do you expect the query to return in that case? (And like Deepak said, show us what you have so far) — sstan, Jun 20 '15 at 03:50
@kolah ghermezi good work on taking the effort to format your question! Just a hint - you can just indent text to make them (contiguous) code blocks + SO automatically adds line breaks when you move to the next line — potatopeelings, Jun 20 '15 at 05:38

potatopeelings · Accepted Answer · 2015-06-20T05:35:01.430

Here is one way to do it

with fullemployee (empno, ename, salary, key)
as
(
  select A.empno, A.ename, A.salary, A.empno || '.' from 
      employee A
  where A.manager is null
  union all
  select C.empno, C.ename, C.salary, D.key || '.' || C.empno from 
      employee C
      inner join fullemployee D on C.manager = D.empno
)
select E.ename, F.ename as manager from fullemployee E
inner join fullemployee F on E.key like F.key || '%' and E.key <> F.key
where E.salary > F.salary

or equivalently

with fullemployee (empno, ename, salary, key)
as
(
   SELECT empno, ename, salary, SYS_CONNECT_BY_PATH(empno, '.') || '.'
   FROM employee
   START WITH manager is null
   CONNECT BY PRIOR empno = manager
)
select E.ename, F.ename as manager from fullemployee E
inner join fullemployee F on E.key like F.key || '%' and E.key <> F.key
where E.salary > F.salary

SQL Fiddle - http://sqlfiddle.com/#!4/37f4ae/35

score 0 · Answer 2 · edited Jun 20 '15 at 04:02

0

This should do the work. Remove that or condition if you don't want the 'root' in your list.

select e.empno, e.ename, e.salary from employee e
    inner join employee mgr on mgr.empno = e.manager
where e.salary > mgr.salary
or (e.manager = mgr.empno)

edited Jun 20 '15 at 04:02

Dan Getz

8,774
6
30
64

answered Jun 20 '15 at 03:56

Martin

644
5
11

The above SQL code to "find ALL the employees having more salary than their managers" is wrong! note that each manager has a hierarchy of managers up to the root i.e. If A is the manager of B and C is the manager of A we can also say that C is the manager of B. suppose that the root is the person with minimum salary, so the result-set should contatin everyone except the root! – kolah ghermezi Jun 20 '15 at 04:47

how to navigate in self loop tables?

2 Answers2