How can I get distinct Manager that should be in Initials result

I have a query which gives me the Manager and Inititials from a specific department, and I need to get the DISTINCT Manager which is also in the query result .. let me elaborate:

My initial Query:

  SELECT Initials, Manager 
  FROM EmploymentUser 
  WHERE Department = 'IT'
  AND (EndDate >= GETDATE() OR DismissedDate >= GETDATE() OR (DismissedDate IS NULL)) 
  AND (LastFlowEditDate IS NOT NULL)
  AND (EmployType = 'fixed') 
  AND (LastDismissFlowDate IS NULL) 
  AND (IsDebugUser IS NULL OR IsDebugUser = '');

This gives me the following result:

|Initials|Manager|
------------------
|DBX     |FPX    |
|GGX     |FPX    |
|BOX     |FPX    |
|LHX     |FPX    |
|FPX     |MPX    |
|SKX     |FPX    |
------------------

I need 1 distinct Manager from the above, but as you can see in the above result I get 2 Managers (MPX & FPX). FPX is the one I need to get, since FBX is also present in the Initials column, which MPX is not .. (In other words : MPX is Manager for FPX, but FPX is Manager for Department IT since he is manager for the rest of the results, and I need the Manager for IT)

How can I achive my desired result? :-)

CodePudding user response：

A self semi join of your results should do the trick:

;with t as (
<your entire select here>
)
select distinct Manager
from t
where exists
(
    select 1
    from t t2
    where t2.Initials=t.Manager
)