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
12
solved SQL Join with unique rows