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

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

CodePudding user response：

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

CodePudding user response：

SELECT *
  FROM Supervisors 
  WHERE  Supervisor NOT IN (SELECT Supervisor FROM Employees)

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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