how to avoid duplicate on Joining two tables
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
Comments
-
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!!