Help with Error: Join Expression Not Supported

[gammaman]

I am trying to write a query using 3 tables. See attached image for joins. In the SQL below, I manually added the code highlighted in red. This is what is giving me the error. Basically, what I want is, even if there is no plant_type '10' in SYSADM.PS_BN_SNAP_PLAN for id '123456789', I still want to get their information from SYSADM.PS_BN_SNAP_PER.

SELECT SYSADM_PS_BN_SNAP_PLAN.EMPLID, SYSADM_PS_BN_SNAP_PER.NATIONAL_ID, SYSADM_PS_BN_SNAP_PER.FIRST_NAME, SYSADM_PS_BN_SNAP_PER.LAST_NAME, SYSADM_PS_BN_SNAP_PER.BIRTHDATE, SYSADM_PS_BN_SNAP_PER.ADDRESS1, SYSADM_PS_BN_SNAP_PER.ADDRESS2, SYSADM_PS_BN_SNAP_PER.CITY, SYSADM_PS_BN_SNAP_PER.STATE, SYSADM_PS_BN_SNAP_PER.POSTAL, SYSADM_PS_BN_SNAP_PER.MAR_STATUS, SYSADM_PS_BN_SNAP_PER.BUSINESS_UNIT, SYSADM_PS_BN_SNAP_PLAN.DEPENDENT_BENEF, SYSADM_PS_BN_SNAP_PER.DT_OF_DEATH, SYSADM_PS_BN_SNAP_PLAN.PLAN_TYPE, SYSADM_PS_BN_SNAP_PER.MEDICARE_EFFDT, SYSADM_PS_BN_SNAP_PER.MEDICARE_A_IND, SYSADM_PS_BN_SNAP_PER.MEDICARE_B_IND, SYSADM_PS_BN_SNAP_PER.MEDICARE_NUMBER, SYSADM_PS_BN_SNAP_PER.STUDENT, SYSADM_PS_BN_SNAP_PER.DISABLED, SYSADM_PS_BN_SNAP_PER.RELATIONSHIP, SYSADM_PS_BN_SNAP_PER.COVERED_PERSON_TYP, Max(SYSADM_PS_BN_SNAP_PLAN.COVERAGE_BEGIN_DT) AS MaxOfCOVERAGE_BEGIN_DT
FROM (041913_ARMS_Manual_Review_2 INNER JOIN SYSADM_PS_BN_SNAP_PER ON [041913_ARMS_Manual_Review_2].[Member SSN] = SYSADM_PS_BN_SNAP_PER.NATIONAL_ID) LEFT JOIN SYSADM_PS_BN_SNAP_PLAN ON (SYSADM_PS_BN_SNAP_PER.EMPLID = SYSADM_PS_BN_SNAP_PLAN.EMPLID) AND (SYSADM_PS_BN_SNAP_PER.DEPENDENT_BENEF = SYSADM_PS_BN_SNAP_PLAN.DEPENDENT_BENEF) AND ('10' = SYSADM_PS_BN_SNAP_PLAN)
WHERE ((([041913_ARMS_Manual_Review_2].[Member SSN])="123456789"))
GROUP BY SYSADM_PS_BN_SNAP_PLAN.EMPLID, SYSADM_PS_BN_SNAP_PER.NATIONAL_ID, SYSADM_PS_BN_SNAP_PER.FIRST_NAME, SYSADM_PS_BN_SNAP_PER.LAST_NAME, SYSADM_PS_BN_SNAP_PER.BIRTHDATE, SYSADM_PS_BN_SNAP_PER.ADDRESS1, SYSADM_PS_BN_SNAP_PER.ADDRESS2, SYSADM_PS_BN_SNAP_PER.CITY, SYSADM_PS_BN_SNAP_PER.STATE, SYSADM_PS_BN_SNAP_PER.POSTAL, SYSADM_PS_BN_SNAP_PER.MAR_STATUS, SYSADM_PS_BN_SNAP_PER.BUSINESS_UNIT, SYSADM_PS_BN_SNAP_PLAN.DEPENDENT_BENEF, SYSADM_PS_BN_SNAP_PER.DT_OF_DEATH, SYSADM_PS_BN_SNAP_PLAN.PLAN_TYPE, SYSADM_PS_BN_SNAP_PER.MEDICARE_EFFDT, SYSADM_PS_BN_SNAP_PER.MEDICARE_A_IND, SYSADM_PS_BN_SNAP_PER.MEDICARE_B_IND, SYSADM_PS_BN_SNAP_PER.MEDICARE_NUMBER, SYSADM_PS_BN_SNAP_PER.STUDENT, SYSADM_PS_BN_SNAP_PER.DISABLED, SYSADM_PS_BN_SNAP_PER.RELATIONSHIP, SYSADM_PS_BN_SNAP_PER.COVERED_PERSON_TYP;

[offie]

You have to give an arrow to the line that has no arrow. Left or right depends on what data you want to get