how to avoid duplicate on Joining two tables

59,806

Solution 1

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

Solution 2

Now that you've clarified your question as:

I want to find all students with a mark of 50 in at least one subject. I would use the query:

SELECT student.id, '50'
FROM student 
WHERE EXISTS (SELECT 1 FROM marks WHERE marks.id = student.id AND marks.mark = 50)

This also gives you flexibility to change the criteria, e.g. at least one mark of 50 or less.

Solution 3

Similar to Charles answer, but you always want to put the predicate (mark=50) in the WHERE clause, so you're filtering before joining. If this is just homework it might not matter but you'll want to remember this if you ever hit any real data.

SELECT std.sid,
       std.name,
       m.mark,
       row_number() over() AS rownum 
 FROM student std 
      JOIN marks m 
        ON std.sid=m.id
WHERE m.mark=50
GROUP BY std.sid, std.name, m.mark
Share:
59,806
zod
Author by

zod

Updated on August 27, 2021

Comments

  • zod
    zod over 2 years
    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!!