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?
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!