CODEWITHSUNDEEP
sql-server-2008
Sonam Mohite
Sonam Mohite

Reputation: 903

How to get records where no other record has specific value

I have a table named Employee as listed below

Employee

In this table i want to write such query which will give me such employee whose reference_type is not 1 

Select * from Employee where Reference_Type <> 1

which will give me all possible values expect '1' but I need output as employee 2 because ithis employee has no Reference_Type 1.

Upvotes: 0

Views: 51

Answers (2)

Kenan Zahirovic
Kenan Zahirovic

Reputation: 1597

Select * from Employee where pk_employee_ID not in 
(select a.pk_employee_ID from Employee a where a. Reference_Type = 1)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

I assume you want all employees where no record has Reference_Type 1, use NOT EXISTS:

SELECT e.*
FROM dbo.Employee e
WHERE NOT EXISTS
(
    SELECT 1 FROM dbo.Employee e2
    WHERE e2.pk_employee_id = e.pk_employee_id 
    AND   e2.Reference_Type = 1
)

Upvotes: 1

Related Questions