Archives of the TeradataForum

Message Posted: Wed, 09 Oct 2013 @ 10:13:48 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi John,

The Table TBL & TBL2 must havehad duplicates on the Join Columns.

     TBL1                TBL2
     KEY_1  KEY_2        KEY_1   KEY_2
         1      2            1       2
         2      3            2       3
         3      4            3       4
         4      5            4       5
         5      6            5       6
                             5       6

So if you join these tables, You will get 6 records instead of 5 records. But if you do a group by on the columns or distinct, you will get 5 records only.

     SELECT TBL1.KEY_1,TBL2.KEY_1,TBL1.KEY_2,TBL2.KEY_2
     FROM
     DB.TBL1 TBL1
     INNER JOIN
     DB.TBL2 TBL2
     ON
     TBL1.KEY_1 = TBL2.KEY_1
     AND
     TBL1.KEY_2 = TBL2.KEY_2
     GROUP BY 1,2,3,4;

     OR

     SELECT TBL1.KEY_1,TBL2.KEY_1,TBL1.KEY_2,TBL2.KEY_2
     FROM
     DB.TBL1 TBL1
     INNER JOIN
     DB.TBL2 TBL2
     ON
     TBL1.KEY_1 = TBL2.KEY_1
     AND
     TBL1.KEY_2 = TBL2.KEY_2
     QUALIFY ROW_NUMBER() OVER( PARTITION BY
     TBL1.KEY_1,TBL2.KEY_1,TBL1.KEY_2,TBL2.KEY_2 ORDER BY
     TBL1.KEY_1,TBL2.KEY_1,TBL1.KEY_2,TBL2.KEY_2)=1;

Thanks & Regards,

Adharssh.

	<Prev	Next>		<<First	<Prev	Next>	Last>>