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:

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.

Answer

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');