Retrieve any data which has only value

Question

Hi,

I got this result using this query.

SELECT employee_id, first_name, last_name, department_id
FROM employees e
WHERE EXISTS (SELECT 'x'
                FROM employees
                WHERE manager_id = e.employee_id)
ORDER BY 1;

Now, I want to retrieve any data only that has a unique value of department_id.

I mean, Steven, Neenda, and Nex are in department 90. So I don't want it.

However Alexander has department 60, and no one has department 60. So I want to retrieve it.

How could I get those data using query?

Thanks in advance!

Answer 1

This might be one option: use your current query as a CTE which is then re-used to

• return desired data
• find department that has only one employee (returned by your query)

---

WITH
   temp   
   AS
   -- this is your current query
      (SELECT employee_id,
              first_name,
              last_name,
              department_id
         FROM employees e
        WHERE EXISTS
                 (SELECT 'x'
                    FROM employees
                   WHERE manager_id = e.employee_id))
SELECT *
  FROM temp
 WHERE department_id IN (  SELECT department_id
                             FROM temp
                         GROUP BY department_id
                           HAVING COUNT (*) = 1);

Answer 2

You appear to want to find "the employees who are managers where there is only a single manager in that department".

You can use the COUNT analytic function so that you do not have to query the same data twice:

SELECT employee_id,
       first_name,
       last_name,
       department_id
FROM   (
  SELECT employee_id,
         first_name,
         last_name,
         department_id,
         COUNT(*) OVER (PARTITION BY department_id) AS num_managers_per_dept
  FROM   employees e
  WHERE  EXISTS (SELECT 'x'
                 FROM employees
                 WHERE manager_id = e.employee_id)
)
WHERE  num_managers_per_dept = 1
ORDER BY employee_id;

Which, for the human_resources schema, outputs:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID
103	Alexander	Hunold	60
108	Nancy	Greenberg	100
114	Den	Raphaely	30
201	Michael	Hartstein	20
205	Shelley	Higgins	110

db<>fiddle here