CODEWITHSUNDEEP
sqloracle-database
Silver Sky
Silver Sky

Reputation: 427

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

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

Upvotes: 4

Views: 148

Answers (6)

Silver Sky
Silver Sky

Reputation: 427

SELECT * 
FROM EmpData
WHERE Employee NOT IN 
(
    SELECT
    DISTINCT(Supervisor)FROM EmpData
    WHERE SupOrEmpFlag = 'e'
)
and SupOrEmpFlag = 's'

Upvotes: 1

MT0
MT0

Reputation: 167774

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

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65105

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

Upvotes: 2

GoonerForLife
GoonerForLife

Reputation: 754

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

Upvotes: 0

M. Abyan Rizqo
M. Abyan Rizqo

Reputation: 21

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

Upvotes: 0

dbCoder
dbCoder

Reputation: 114

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

Upvotes: 0

Related Questions