Duplicate values on join-CodePudding

I am trying to create a table using the following query:

PROC SQL;
    CREATE TABLE NEW_TABLE AS
        SELECT A.* ,B.FIELD_1, B.FIELD_2
            FROM TEST AS A
                LEFT JOIN OTHER_TABLE AS B ON A.CONTRACT = B.CONTRACT
                    AND (A.CUSTOMER = B.CUSTOMER OR A.CUSTOMER_NEW = B.CUSTOMER);
QUIT;

but that query returns duplicate values. I am guessing that the OR i am using on the join is causing that because in some cases it will match one of the conditions or both. Also i am removing duplicates on OTHER_TABLE before i do the join. How can i make it match A.CUSTOMER = B.CUSTOMER and then A.CUSTOMER_NEW = B.CUSTOMER only if it didn't find a match for the first one? I am using left join because i want to keep all the records in TEST and get null values when it does not find anything even after checking those conditions.

CodePudding user response：

Try two left joins.

proc sql;
create table new_table as
  select a.*
       , case when (missing(b.customer)) then c.field_1 else b.field_1 as field_1
       , case when (missing(b.customer)) then c.field_2 else b.field_2 as field_2
from test as a
left join other_table as b
  on a.customer = b.customer and a.contract = b.contract
left join other_table as c
  on a.customer_new = c.customer and a.contract = c.contract
;
quit;

CodePudding user response：

you use left join , make sure all fields are not the same, maybe one field are different