Using inner join, left outer join, cross apply get syntax error with Where Clause

Question

We have a third party database. The contact information is contained in many tables: contact1, contact2, contsupp. Using inner join, left outer join, cross apply get syntax error with Where Clause. The query works without the left outer join. But I need that to select the correct email. Where should the where clause go in this case?

            SELECT 'P', 
            'NA' PEOPLE_ID,
            'NA' PEOPLE_CODE_ID, 
            T.ForeName, 
            T.Middle_Name, 
            T.Surname, 
            'PERM', 
            'N', 
            GetDate(), 
            GetDate(), 
            'SCTBRDG', 
            '0001', 
            GetDate(), 
            GetDate(), 
            'SCTBRDG', 
            '0001', 
            '*', 
            C2.UBIRTHDATE, 
            C2.Ubrthcnty, 
            CASE WHEN LEFT([URELEASE], 6) IN ('Employer', 'EMPLYR') THEN 'EMPLYR' ---was URELEASE
             WHEN LEFT([URELEASE], 6) IN ('Family', 'Sister', 'OFAM') THEN 'OFAM'
             WHEN LEFT([URELEASE], 6) = 'FATHER' THEN 'FATHER'
             WHEN LEFT([URELEASE], 6) IN ('Friend', 'OTHER') THEN 'OTHER'
             WHEN LEFT([URELEASE], 6) IN ('Guardian', 'GUARDN') THEN 'GUARDN'
             WHEN LEFT([URELEASE], 6) = 'MOTHER' THEN 'MOTHER'
             WHEN LEFT([URELEASE], 6) IN ('PARENT', 'Parents') THEN 'PARENT'
             WHEN LEFT([URELEASE], 6) = 'RESTR' THEN 'RESTR'
             WHEN LEFT([URELEASE], 6) = 'Spouse' THEN 'Spouse'
             WHEN LEFT([URELEASE], 6) IS NULL THEN 'NROF'
             ELSE 'NONE' END AS ReleaseInfo, 
            C2.Ugender, 
            C2.Umarstat, 
            C2.Udenominat, 
            C2.Uveteran, 
            C2.Ucntrycitz, 
            C2.Uvisatype, 
            i.ACCOUNTNO, 
            i.ADDRESS1, 
            i.ADDRESS2, 
            i.ADDRESS3, 
            i.CITY, 
            i.STATE, 
            i.ZIP, 
            i.PHONE3, 
            i.FAX, 
            C2.UCOUNTY, 
            ISNULL(C2.UEMAILSCT, replace(lower(T.ForeName) + '.' + lower(T.Surname) + '@okwstudents.edu', ' ', '')),
            cs.CONTSUPREF as EMAIL,                 --Personal Email
            C2.UADDRTYPE, 
            i.KEY2, 
            i.KEY5, 
            i.KEY3, 
            C2.UCURRICULM, 
            C2.UMAJOR, 
            i.DEPARTMENT, 
            C2.UCOLLCMPLT, 
            C2.UFULLPART, 
            C2.UCOHORT, 
            C2.UAPPLICDT, 
            C2.USTUSTATDT, 
            C2.UDECISCODE, 
            C2.UDECISDATE, 
            C2.USTRTTRMDT, 
            C2.UCOHRTSTDT, 
            C2.UADMCOUNCD, 
            C2.UADVISMNTR, 
            i.Phone1, 
            i.Phone2, 
            Uethnicbg, 
            i.COUNTRY,
            C2.USOCSECNUM GOVERNMENT_ID,
            i.KEY1,
            C2.Urelease RELEASE_INFO,
            C2.USOCSECNUM TAX_ID, ---
            C2.USOCSECNUM, ---
            GETDATE()
            FROM 
            --inserted i ---test inserted, 
            GoldMine_test.dbo.CONTACT1 i
            Inner Join CONTACT2 C2 on (i.ACCOUNTNO = C2.ACCOUNTNO)
            CROSS APPLY dbo.NameParser(i.Contact) T
            --inner join GoldMine_test.dbo.CONTACT2 c2 on ( i.ACCOUNTNO = c2.ACCOUNTNO ) 
            LEFT outer join GoldMine_test.dbo.CONTSUPP cs on (  (cs.ACCOUNTNO = i.ACCOUNTNO) 
            AND (cs.RECTYPE='P') 
            AND ( cs.U_CONTACT = 'E-MAIL ADDRESS')
            WHERE i.Key1 = '31-ATE' --Incorrect syntax near the keyword 'WHERE'
            OR (i.Key1 = '50-DEP')
            OR (i.Key1 = '51-RET')

Answer 1

Probably the syntax error is in this line of code:

LEFT outer join GoldMine_test.dbo.CONTSUPP cs on (  (cs.ACCOUNTNO = i.ACCOUNTNO) 

Try to replace it with this one:

LEFT outer join GoldMine_test.dbo.CONTSUPP cs on (cs.ACCOUNTNO = i.ACCOUNTNO) 

And check if it works