Search code examples
sqljoindb2row-number

how to avoid duplicate on Joining two tables

Student Table

 SID    Name
 1      A
 2      B
 3      C

 Marks Table

 id mark    subject 
 1  50  physics
 2  40  biology
 1  50  chemistry
 3  30  mathematics



SELECT distinct(std.id),std.name,m.mark, row_number() over() as rownum FROM 

student std JOIN marks m ON std.id=m.id AND m.mark=50

This result is 2 times A even after using disticnt . My expected result will have only one A. if i remove row_number() over() as rownum its working fine. Why this is happening ? how to resolve. AM using DB2!!

Solution

  • There are two rows in marks Table with id = 1 and mark = 50.. So you will get two rows in the output for each row in student table... If you only want one, you have to do a group By 

     SELECT std.id, std.name, m.mark, row_number() 
  over() as rownum 
 FROM student std 
    JOIN marks m 
       ON m.id=std.id AND m.mark=50 
 Group By std.id, std.name, m.mark