, (SELECT DISTINCT b1.FirstName + ' ' + b1.LastName
FROM dbo.consultant
LEFT OUTER JOIN dbo.uvwConsultantDownLine AS B1
ON D.SponsorID = B1.ConsultantID) AS SponsorName
the left outer join is the problem. it's going to return all the distinct records dbo.consultant regardless of any corresponding SponsorId