SQL Join on multiple OR conditions returns duplicates -

i have join returning multiple rows. need modify code not so. i've figured out extremely inefficient method, , hoping point me in right direction.

my dataset consists of list of 50 agents in agent cte take technical support tickets stored in tickets table. agent info stored in employee table. need join 3 objects each ticket associated it's agent. agent ownership of specific support call can determined 1 or more fields

-initial ticket responder -ticket creator -ticket owner 

the complication 1 or more of 50 agents can show in 1 of these 3 fields. performing join using syntax results in duplicates.

with cte_agents (agent_name, agent_region) --defines in-scope agents , agent region project  select   fi.servicerequestnumber in_scope_ticket join vwdimemployee e1 (nolock) on  e1.employeederivedid = fi.initialresponseowneremployeederivedid or e1.employeederivedid = fi.creatoremployeederivedid or e1.employeederivedid = fi.currentowneremployeederivedid join cte_agents ag on e1.employeeemail = ag.agent 

because multiple in-scope agents can in multiple join fields above query returns duplicates. example, if agent 1 both ticket creator , ticket owner 2 results when want one. if agent 3 ticket owner , agent 4 ticket creator specific ticket twice in result set.

in case of such duplicates, want return single row. current solution is:

select  e1.employeename initalowner ,e2.employeename ticketcreator ,e3.employeename ticketowner  join vwdimemployee e1 (nolock) on e1.employeederivedid = st.initialresponseowneremployeederivedid join vwdimemployee e2 (nolock) on e2.employeederivedid = st.creatoremployeederivedid join vwdimemployee e3 (nolock) on e3.employeederivedid = st.currentowneremployeederivedid 

in case of multiple results, use bit of code give current owner precedence. (results pumped through multiple ctes in order this).

isnull(initial_task_agent,isnull(creator_task_agent,current_task_agent)) cce_agent ,isnull(initial_region,isnull(creator_region,current_region)) cce_region 

i more efficient way this, can't figure out how. ideas appreciated.

you find coalesce() works better isnull() here:

coalesce(initial_task_agent,creator_task_agent,current_task_agent) cce_agent ,coalesce(initial_region,creator_region,current_region) cce_region 

coalesce() acts isnull, can list more 2 parameters. other that, it's not bad approach.