Joins

Database
Highlighted
Enthusiast

Joins

Hi,

Sorry to ask you a simple qn..

Input data is given below.

Table A                     Table B

COL 1  COL2            COL1

-------------------           ---------

TTT      POL               POL

TTT     HUN               HUN

TTT     IDN                 IDN

TTT     JPN               JPN

VVV     HUN

YYY     POL

ZZZ      IDN

 

How to create a join of these two tables to get this result.

COL 1  COL2    

-------------------      

TTT      POL           

TTT     HUN          

TTT     IDN          

TTT     JPN            

 

Thanks.

2 REPLIES
Teradata Employee

Re: Joins

Select MIN(A.Col1), A.Col2
From TableA A, TableB B
Where A.Col2 = B.Col1
Group by A.Col2

although the data doesn't show why you need the join, so maybe this would work:
Select MIN(A.Col1), A.Col2
From TableA A
Group by A.Col2

Teradata Employee

Re: Joins

You can try QUALIFY with ROW_NUMBER

 

SELECT A.COL1, B.COL2

FROM TABLE A 

JOIN TABLE B

ON A.COL2 = B.COL1

QUALIFY ROW_NUMBER() OVER (PARTITION BY A.COL2 ORDER BY A.COL1) =1

 

I hope it will answer you question,