Case statement in Join Clause

Question

I am trying to get data from multiple tables but not sure how to do that. Below is a query which I am using but its not working. Please suggest.

SELECT DISTINCT 
        VWL.Type, VWL.ID AS CompanyID,VWL.Name
    FROM dbo.LeadList_New AS VWL 
    LEFT OUTER JOIN
        CASE VWL.Type WHEN 'COMPANY' THEN
            CRMCompanyEmailAddress AS CE ON VWL.ID=CE.CRMCompanyID
        WHEN 'PERSON' THEN
            PersonEmailAddress AS PE ON VWL.ID=PE.PersonID
        END

Answer 1

You cannot join tables with a CASE, maybe you want this instead:

SELECT DISTINCT VWL.Type, VWL.ID AS CompanyID, VWL.Name, 
                CASE WHEN VWL.type = 'COMPANY' 
                    THEN CE.Name 
                    ELSE PE.Name 
                END AS EntityName 
FROM   dbo.leadlist_new AS VWL 
       LEFT OUTER JOIN crmcompanyemailaddress AS CE 
                    ON VWL.id = CE.crmcompanyid 
       LEFT OUTER JOIN personemailaddress AS PE 
                    ON VWL.id = PE.personid