Single query filtering-CodePudding

I have the following two tables:

Manager table:

id
1
2
3

Employee table:

id	manager	Status
4	3	ACTIVE
5	3	ACTIVE
6	3	INACTIVE
7	1	ACTIVE
8	1	ACTIVE

Is it possible to select in a single query only managers which have all employees with status = 'ACTIVE'?

For example, I want only manager with id 2, and not manager with id 3 (having employee with id 6 with status = 'INACTIVE').

Thank you for your time!

CodePudding user response：

The following query returns Managers having ACTIVE employees and no INACTIVE employees. Managers with employees are excluded.

SELECT m.id 
FROM Manager m
JOIN Employee e
on m.id = e.manager
WHERE status = 'ACTIVE'
AND m.id NOT IN
  (SELECT manager 
   FROM employee
   WHERE Status = 'INACTIVE')
GROUP BY m.id;

The following query returns Managers not having INACTIVE employees, which means that is returns managers with no employees.

SELECT id 
FROM Manager
WHERE id NOT IN
  (SELECT manager 
   FROM employee
   WHERE Status = 'INACTIVE');

CodePudding user response：

You could try this:

select ma.id 
from manager ma
cross apply (select count(*) c from employee where ma.manager = employee.code)q1
cross apply (select count(*) c from employee where ma.manager = employee.code and employee.status = 'ACTIVE') q2
where q1.c = q2.c

You are just checking if the count of all employees is the same with the count of all active employees, for the same manager of course

CodePudding user response：

You can GROUP BY the manager_id and then use conditional aggregation to find the groups where there are active employees and no inactive employees:

SELECT manager
FROM   employees
GROUP BY manager
HAVING COUNT(CASE status WHEN 'ACTIVE' THEN 1 END) > 0
AND    COUNT(CASE status WHEN 'INACTIVE' THEN 1 END) = 0;

Which, for the sample data:

CREATE TABLE managers (id) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;

CREATE TABLE employees (id, manager, Status) AS
SELECT 4, 3, 'ACTIVE' FROM DUAL UNION ALL
SELECT 5, 3, 'ACTIVE' FROM DUAL UNION ALL
SELECT 6, 3, 'INACTIVE' FROM DUAL UNION ALL
SELECT 7, 1, 'ACTIVE' FROM DUAL UNION ALL
SELECT 8, 1, 'ACTIVE' FROM DUAL;

Outputs:

MANAGER

1

If you want to include all managers, even those without employees, then you can use:

SELECT m.id AS manager
FROM   managers m
       LEFT OUTER JOIN employees e
       ON (e.manager = m.id AND e.status = 'INACTIVE')
WHERE  e.manager IS NULL;

Which outputs:

MANAGER

2

1

MANAGER
2
1

db<>fiddle here

CodePudding user response：

This query returns manager 1 and 2;

SELECT m.id 
FROM managers m
WHERE NOT EXISTS (
    SELECT 1 
    FROM employees e 
    WHERE m.id = e.manager 
        AND e.Status = 'INACTIVE' 
        AND ROWNUM =1);

But manager 2 has no employe wheter active or inactive. If you want to eliminate manager 2;

SELECT m.id 
FROM managers m
WHERE NOT EXISTS (
        SELECT 1 
        FROM employees e 
        WHERE m.id = e.manager 
            AND e.Status = 'INACTIVE' 
            AND ROWNUM = 1)
    AND EXISTS (
        SELECT 1 
        FROM employees e 
        WHERE m.id = e.manager 
            AND e.Status = 'ACTIVE' 
            AND ROWNUM = 1);

2nd query may lead a performance issue due to having more than one sub queries. You can prefer join if you want to eliminate manager 2. But I give second query to show an example of this approach anyway.