Exclude a specific match in SQL-CodePudding

Good Morning I am fairly new to the world of SQL, and stuck on an issue. I have been searching forums but not really found an answer. I have 2 tables Incidents, which holds all the information for an IT Incident and Employee, which holds all the Employee information. The Incident table has the Name of the person who resolved the Incident, but not the team they work in. I have created a join, as both tables have the UserID and I am trying to bring back the Team from the Employee table, however several of the Employees have 2 entries, one is the correct team and the other is "Self Service". My question is, is there a way to always ignore any match of "Self Service", and just use the other record? Any help greatly appreciated Thanks

Apologies, I'm using SSMS v14 The code I have so far is

SELECT 
    i.CreatedBy, 
    COALESCE (e.Team, e1.Team) as CreatedByTeam, 
    i.CreatedDateTime, 
    i.Owner, 
    i.OwnerTeam, 
    i.LastModDateTime, 
    i.ResolvedBy,
    COALESCE (e2.Team, e3.Team) as ResolvedByTeam
FROM 
    Incident i
    LEFT JOIN Employee e ON i.CreatedBy=e.DisplayName
    LEFT JOIN Employee e1 ON i.CreatedBy=e1.PrimaryEmail
    LEFT JOIN Employee e2 ON i.ResolvedBy=e2.DisplayName
    LEFT JOIN Employee e3 ON i.ResolvedBy=e3.PrimaryEmail
WHERE 
    i.CreatedDateTime BETWEEN '2021-06-09' AND '2022-06-09' OR i.LastModDateTime BETWEEN '2021-06-09' AND '2022-06-09'

And it is the 2 COALESCEs that are returning the Self Service results

CodePudding user response：

You can filter out the Self Service records using a WHERE statement, preferrably only doing it once using a cte:

WITH pure_employee AS (
SELECT * FROM Employee WHERE Team <> 'Self Service'
)

SELECT 
    i.CreatedBy, 
    COALESCE (e.Team, e1.Team) as CreatedByTeam, 
    i.CreatedDateTime, 
    i.Owner, 
    i.OwnerTeam, 
    i.LastModDateTime, 
    i.ResolvedBy,
    COALESCE (e2.Team, e3.Team) as ResolvedByTeam
FROM 
    Incident i
    LEFT JOIN pure_employee e ON i.CreatedBy=e.DisplayName
    LEFT JOIN pure_employee e1 ON i.CreatedBy=e1.PrimaryEmail
    LEFT JOIN pure_employee e2 ON i.ResolvedBy=e2.DisplayName
    LEFT JOIN pure_employee e3 ON i.ResolvedBy=e3.PrimaryEmail
WHERE 
    i.CreatedDateTime BETWEEN '2021-06-09' AND '2022-06-09' OR i.LastModDateTime BETWEEN '2021-06-09' AND '2022-06-09'