CODEWITHSUNDEEP
sqloracle-database
Shury
Shury

Reputation: 578

Employees without subordinates

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);

Upvotes: 1

Views: 1955

Answers (4)

Alexandra_p
Alexandra_p

Reputation: 13

SELECT *
 FROM employees e
 WHERE e.employee_id NOT IN ( SELECT nvl(manager_id, 0)
                              FROM employees );

Upvotes: 0

xQbert
xQbert

Reputation: 35353

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)

Upvotes: 0

John Bollinger
John Bollinger

Reputation: 181932

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.)

Upvotes: 2

npe
npe

Reputation: 15729

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)

Upvotes: 4

Related Questions