CODEWITHSUNDEEP
sqldatabaseoracle-database
James Henrick
James Henrick

Reputation: 71

Hierarchical Query( how to retrieve middle nodes)

Good evening, I need help trying to retrieve the middle-class managers from one of my hierarchical tables (circled in red), who are not root node nor leaf node.

Hierarchical chart/Data Hierarchical Chart Data

I was able to retrieve all the leaf nodes using my query 

select level,  first_name ||' '|| last_name "FullName" 
from more_employees
where employee_id not in (select NVL(manager_id, 0) from more_employees)
start with employee_id = 1
CONNECT by prior employee_id = manager_id;

Result

I'm sure I should use the keyword "in" in my where clause, but keep getting a different result.

Upvotes: 2

Views: 517

Answers (2)

Valentin Kuzub
Valentin Kuzub

Reputation: 12093

Something like this should work I believe

select * from more_employees m1 where manager_id is not null
and exists ( select 1 from more_employees m2 where m2.manager_id=m1.employee_id)

Upvotes: 2

user330315
user330315

Reputation:

You can use the CONNECT_BY_IS_LEAF pseudo column for this. 

select level,  first_name ||' '|| last_name "FullName" 
from more_employees
where connect_by_isleaf = 0 and manager_id is not null
start with employee_id = 1
connect by prior employee_id = manager_id;

You can also use that to get all leafs:

select level,  first_name ||' '|| last_name "FullName" 
from more_employees
where connect_by_isleaf = 1
start with employee_id = 1
connect by prior employee_id = manager_id;

Which is probably faster than your solution with a sub-select

Here is an SQLFiddle example: http://sqlfiddle.com/#!4/511d9/2

Upvotes: 2

Related Questions