[Solved] SQL Join with unique rows

Question

Ok, let’s see if this answer suits your request.

SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,'YYYY-MM-DD') EFFDT, b.DEDCD as DEDCD2,GTN 
   FROM 
   (
   select  EFFDT,GTN,EMPLID,DEDCD,
        row_number() over (partition by EMPLID order by DEDCD) rn
 from table1 ) A
    LEFT OUTER JOIN 
    (
   select  EFFDT,EMPLID,DEDCD,
        row_number() over (partition by EMPLID order by DEDCD) rn
 from table2       
    ) B
 ON ( A.EMPLID=B.EMPLID AND A.EFFDT=B.EFFDT AND a.rn=b.rn)

I’m assuming here you’re just interested in having side by side the info of the two tables linked by EMPLID and EFFDT, and that table1 and table2 will always have the same record number for the same key.

I’ve created an example for you to test at rextester.com.

Edited: new version

I’ve tried to manage also the new “requirements” that have been submitted here as comments.
It’s getting complicated, but I hope that’s what you need:

 SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,'YYYY-MM-DD') EFFDT, b.DEDCD as DEDCD2,GTN
    FROM 
    (
    select  t1.EFFDT,t1.GTN,t1.EMPLID,t1.DEDCD,
         row_number() over (partition by t1.EMPLID order by case when t2.EMPLID is not null then 0 else 1 end asc, t1.DEDCD) rn
  from table1 t1 left join (select distinct EMPLID,   EFFDT, DEDCD from    table2) t2 on  t1.EMPLID=t2.EMPLID AND t1.EFFDT=t2.EFFDT  and t1.DEDCD=t2.DEDCD
        ) A
     LEFT OUTER JOIN 
     (
    select  t2.EFFDT,t2.EMPLID,t2.DEDCD,
         row_number() over (partition by t2.EMPLID order by case when t1.EMPLID is not null then 0 else 1 end asc, t2.DEDCD) rn
  from table2 t2  left join (select distinct EMPLID,   EFFDT, DEDCD from    table1) t1 on  t1.EMPLID=t2.EMPLID AND t1.EFFDT=t2.EFFDT and t1.DEDCD=t2.DEDCD
     ) B
  ON ( A.EMPLID=B.EMPLID AND A.EFFDT=B.EFFDT AND a.rn=b.rn)

I’ve created an example for you to test at rextester.com

Accepted Answer