join-case

SELECT trd.* ,(CASE   WHEN ta.AccountID IS null THEN  '' ELSE ta.AccountID END ) FROM traceroleid trd LEFT JOIN traceaccountmap ta  on

 (CASE trd.Role WHEN 1 then ta.InvestConsultantID when 2 THEN ta.InvestManagerID WHEN 3 THEN ta.TraderID END  ) =trd.TraceRoleID