CODEWITHSUNDEEP
sqljoindb2row-number
zod
zod

Reputation: 12417

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!!

Upvotes: 7

Views: 83053

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146499

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

Upvotes: 8

orbfish
orbfish

Reputation: 7731

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

Upvotes: 1

sceaj
sceaj

Reputation: 1643

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.

Upvotes: 6

Related Questions