CODEWITHSUNDEEP
sqlsql-server
Nick Matveev
Nick Matveev

Reputation: 5

Microsoft SQL Server : how to filter out results of a 2nd query from the first query

Let's say I have data that looks like this:

Data example

I have some records that are retired and some that are active. The problem is some of my records have both retired and active rows. I only want records that do not have an active row.

So in this example despite the fact that Greg has 2 retired rows. The fact that one of his rows is active would disqualify him. Tommy on the other hand only has 1 row and it is retired. He doesn't have an active row.

The query would return only Tommy.

Upvotes: 0

Views: 48

Answers (2)

Erechtheus
Erechtheus

Reputation: 765

SELECT * FROM myTable 
WHERE Status = 'RETIRED'
AND [Employee Name] NOT IN -- Assumes that Employee Name is a unique key
(
SELECT [Employee Name] FROM myTable 
WHERE Status = 'ACTIVE'
)

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23837

Per your definition, employee's could just be defined by name. Then you can use EXISTS. ie:

select * from myTable t1
where Status = 'RETIRED' and 
     not exists (
   select * from myTable t2 
   where t1.EmployeeName = t2.employeeName and t2.Status = 'ACTIVE');

Upvotes: 1

Related Questions