SQL: Select records where value does not belong to a certain column

user_11325405 0
Viewed 85

I want to select those Supervisors that are not supervising any employee e.g. Sup4

Note: All the supervisors are employee themselves so the are in Employee Column but as the supervisors are not supervised by any one so the corresponding Supervisors Column is null.

Table: EmpData

PK Employee Supervisor SupOrEmpFlag
1 EmpA Sup1 e
2 Sup1 null s
3 EmpB Sup2 e
4 Sup2 null s
5 EmpC Sup3 e
6 Sup3 null s
7 Sup4 null s

I know a better approach would be to create a separate table for both Employee and Supervisor but I am just curious if there is any approach using join that I am missing.

I have tried following but it returns 0 records.

Executed in Oracle Live SQL:

CREATE TABLE EmpData(
    PK number(38) GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) PRIMARY KEY,
    Employee varchar2(100) NOT NULL,
    Supervisor varchar2(100),
    SupOrEmpFlag varchar2(100) NOT NULL
);


INSERT INTO EmpData (Employee , Supervisor, SupOrEmpFlag) 
SELECT 'EmpA', 'Sup1', 'e' FROM dual UNION ALL 
SELECT 'Sup1',   null, 's' FROM dual UNION ALL 
SELECT 'EmpB', 'Sup2', 'e' FROM dual UNION ALL 
SELECT 'Sup2',   null, 's' FROM dual UNION ALL 
SELECT 'EmpC', 'Sup3', 'e' FROM dual UNION ALL 
SELECT 'Sup3',   null, 's' FROM dual UNION ALL 
SELECT 'Sup4',   null, 's' FROM dual

SELECT *
  FROM EmpData sup
  JOIN EmpData emp
    on emp.Employee = sup.Supervisor
   and sup.SupOrEmpFlag = 's'
  JOIN EmpData nemp
    on nemp.Employee = emp.Employee
   and nemp.Employee <> emp.Employee
oracle sql
6 Answers

One option would be determining through use of hierarchical query such as

 SELECT NVL(supervisor,employee) AS supervisor
   FROM EmpData e
CONNECT BY PRIOR employee = supervisor
  GROUP BY NVL(supervisor,employee)
 HAVING MAX(level) = 1

or using a query having a conditional aggregation provided for HAVING clause such as

 SELECT NVL(supervisor,employee) AS supervisor
   FROM EmpData e
  GROUP BY NVL(supervisor,employee) 
 HAVING MAX(CASE WHEN suporempflag = 's' AND supervisor IS NULL THEN 0 ELSE 1 END) = 0

Demo

user_58413060 
SELECT * 
FROM EmpData
WHERE Employee NOT IN 
(
    SELECT
    DISTINCT(Supervisor)FROM EmpData
    WHERE SupOrEmpFlag = 'e'
)
and SupOrEmpFlag = 's'
user_113254050 
SELECT *
  FROM Supervisors 
  WHERE  Supervisor NOT IN (SELECT Supervisor FROM Employees)
user_15888270

First you must get list supervisor that in column Supervisor. Then get list of employee that not in the first list and have flag "s".

The query will be like this.

SELECT Employee
FROM EmpData 
WHERE Employee NOT IN (SELECT DISTINCT Supervisor FROM EmpData) AND SupOrEmpFlag = "s";
user_172986350

Tyr this.

WITH SupervisorList AS
(
  SELECT PK, EMPLOYEE
  FROM EmpData
  WHERE SupOrEmpFlag = 's'
)
SELECT s.*
FROM SupervisorList S
LEFT JOIN EmpData E
  ON S.EMPLOYEE = E.Supervisor
WHERE E.PK IS NULL
user_175897810

You can do it with a hierarchical query without aggregating using:

SELECT *
FROM   empdata
WHERE  LEVEL = 1
AND    CONNECT_BY_ISLEAF = 1
START WITH SupOrEmpFlag = 's'
CONNECT BY PRIOR employee = supervisor;

Which, for the sample data, outputs:

PK EMPLOYEE SUPERVISOR SUPOREMPFLAG
7 Sup4 null s

db<>fiddle here

user_15092640
Related