1

I'm trying to print all the employees that don't have subordinates.

enter image description here

I have been thinking about a tree data structure. Practically, most of the employees have subordonates (those are called managers). The only ones without subordonates are the leafs (they don't have any children).

However, I don't understand how can I select the leafs from this tree.

--following prints employees without manager.
    SELECT e.employee_id, e.last_name, e.first_name
    FROM employees e
    WHERE e.employee_id = (SELECT employee_id FROM employees WHERE manager_id IS NULL AND employee_id = e.employee_id);
Shury
  • 468
  • 3
  • 15
  • 49
  • use [connect by prior](http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm) and check the leaf node status. [The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy.](http://docs.oracle.com/cd/B12037_01/server.101/b10759/pseudocolumns001.htm#i1007332) – xQbert Apr 28 '15 at 20:42
  • possible duplicate of [get ALL last level children (leafs) from a node (hierarhical queries Oracle 11G)](http://stackoverflow.com/questions/21671568/get-all-last-level-children-leafs-from-a-node-hierarhical-queries-oracle-11g) – xQbert Apr 28 '15 at 20:52

4 Answers4

4

In short, you want to select all employees, who don't act as managers for other employees. That means, you want to select such employees, whose employee_id is not used as manager_id for any other employee.

Try this:

SELECT *
  FROM employees e
 WHERE NOT EXISTS (SELECT 1
                     FROM employees e2
                    WHERE e2.manager_id = e.employee_id)
npe
  • 15,395
  • 1
  • 56
  • 55
2

You can do this via an outer join:

SELECT e.employee_id, e.last_name, e.first_name
FROM
  employees e
  LEFT JOIN employees sub
    ON e.employee_id = sub.manager_id
WHERE sub.manager_id IS NULL

The filter condition selects only those rows of the left table that have no matching rows in the right table.

This is preferable to filtering via a correlated subquery, as the latter may require performing the subquery separately for every single employee row. (If the query planner avoids that, it will be by transforming it into an equivalent of the outer join.)

John Bollinger
  • 160,171
  • 8
  • 81
  • 157
0
SELECT e.employee_ID, e.last_name, e.First_name, CONNECT_BY_ISLEAF "IsLeaf",
   LEVEL, SYS_CONNECT_BY_PATH(e.employee_ID, '/') "Path"
   FROM employees e
   CONNECT BY PRIOR E.employeeID = E.Manager_ID;
   where isLeaf =1

Basically stolen from help docs: http://docs.oracle.com/cd/B12037_01/server.101/b10759/pseudocolumns001.htm#i1007332

or another stack question: get ALL last level children (leafs) from a node (hierarhical queries Oracle 11G)

Community
  • 1
  • 1
xQbert
  • 34,733
  • 2
  • 41
  • 62
0
SELECT *
 FROM employees e
 WHERE e.employee_id NOT IN ( SELECT nvl(manager_id, 0)
                              FROM employees );
Alexandra_p
  • 13
  • 4