R Prasad R Prasad - 1 month ago 6
SQL Question

SQL Join with unique rows

I have two tables
Table1

- EMPLID XX_EMPLID GTN DEDCD EFFDT
1 A1 102 XXYY 02-OCT-16
1 A1 103 XXYZ 02-OCT-16


Table2

- EMPLID DEDCD EFFDT
1 XXYA 02-OCT-16
1 XXYZ 02-OCT-16


When I join the select should return only two rows and the output should be

- EMPLID 2.DEDCD EFFDT 1.DEDCD
1 XXYA 02-OCT-16 XXYY
1 XXYZ 02-OCT-16 XXYZ


EMPLID,EFFDT being the key. I do not have want to join DEDCD as I need to the see the difference if any. Please suggest.

Simple join will give me more rows always. I am looking to limit the row to the number of rows in Table1. I tried "Right outer join" and "left outer join" but does not work

After some help, I am able to get close to the results. However, one issue that I am facing is pointed out below

10008536 XXYZ 06-MAR-02 (XXYY) 06-MAR-02
10008536 (XXYY) 06-MAR-02 XXZZ 06-MAR-02

Answer

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