Merging two tables with duplicates assigned to certain value in mssql

Question

I have two tables, Table-1:

     col4  col5                                                                                                                  
0    Tom    20                                                                                                                   
1   nick    21                                                                                                                   
2  krish    19                                                                                                                   
3   jack    18

Table-2:

   col1   col2  col3                                                                                                              
0  xxx    Tom    110                                                                                                              
1  yyy   nick    121                                                                                                              
2  zzz  krish    119                                                                                                              
3  xxx   jack    118                                                                                                              
4  yyy    Tom    141                                                                                                              
5  xxx   nick    156                                                                                                              
6  yyy  krish    176                                                                                                              
7  zzz   jack    186

I need to have col5 in my table-2 based on col2 and col4. The value of col5 for the repeated values in col2 should be assigned 0, except for the first value. For example, Tom is getting repeated and only the first value of Tom(index 0) is assigned to 20 after merging with table-1. The other value of Tom should be 0 (index 4). I want the resultant table column as :

  col1   col2  col3  col5                                                                                                            
0  xxx    Tom   110   20                                                                                                           
1  yyy   nick   121   21                                                                                                          
2  zzz  krish   119   19                                                                                                           
3  xxx   jack   118   18                                                                                                           
4  yyy    Tom   141    0                                                                                                            
5  xxx   nick   156    0                                                                                                          
6  yyy  krish   176    0                                                                                                         
7  zzz   jack   186    0

Please help me to achieve this. Thanks in advance.

Answer 1

SQL tables represent unordered sets. There is no "first" row unless a column specifies the ordering.

Assuming you have such a column, you can just use left join and row_number() like this:

select t2.*,
       (case when row_number() over (partition by t2.col4 order by ?) = 1
             then t2.col5 else 0
        end) as col5
from table2 t2 left join
     table1 t1
     on t2.col4 = t1.col2;

Answer 2

First of all, join two tables normally:

SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.col4=Table1.col2

Then the result is clear:

  col1   col2  col3  col5                                                                                                            
0  xxx    Tom   110   20                                                                                                           
1  yyy   nick   121   21                                                                                                          
2  zzz  krish   119   19                                                                                                           
3  xxx   jack   118   18                                                                                                           
4  yyy    Tom   141   20                                                                                                            
5  xxx   nick   156   21                                                                                                          
6  yyy  krish   176   19                                                                                                         
7  zzz   jack   186   18

After that, let's add a row number for each person, and select just first one:

SELECT t.col1,t.col2,t.col3, CASE WHEN t.Rn=1 THEN t.col5 ELSE 0 END col5
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Table1.col4 ORDER BY Table2.Col1) Rn
FROM Table1
INNER JOIN Table2 ON Table1.col4=Table1.col2) t