Try this:
SELECT
emp.eid, emp.agentid,
info.emailid,
mail.EmailAddr, mail.LName, mail.FName
FROM employees AS emp
outer apply
(select top (1) emailid
from employeeinfo ei
where
ei.agentid = emp.agentid and
(stDate <= dateadd(dd, datediff(dd, 0, getdate()), 0)
and
endDate > dateadd(dd, datediff(dd, 0, getdate()), 0)
)
order by endDate DESC, modDt Desc
) AS info
outer apply
(select top 1 EmailAddr, LName, FName
from employeemail x
where CAST(info.emailID + '@company1.com' as varchar(255)) = x.mail
) As mail
Where emp.agent = info.agent
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.