Removing duplicates from a table linked to another table

Question

Student
_______________________
SudentId   Name
101        Peter
103        John
112        James
116        Peter
117        Peter


SudentId_Subject
_______________________
SudentId Subject
101      Physics
103      Chemistry
112      Mathematics
116      Physics
117      Commerce

To find duplicate students, I tried the query

select * from Student group by Name having count(*)>1, got

SudentId   Name
_______________________
101        Peter
116        Peter
117        Peter

Actually, I need to filter out students with the same subject. those.

101        Peter
116        Peter

are duplicates (the same subject), but not 117 Peter

How can I write a query for this?

Answer 1

I may have complicated things, but I could not think of an easier way:

SELECT ss.student_id,ss.name
FROM student ss
JOIN studentid_subject tt
 ON(ss.student_id = tt.student_id)
JOIN(SELECT t.name,s.department
     FROM student t
     JOIN studentid_subject s
      ON(t.student_id = s.student_id)
     GROUP BY t.name,s.department
     HAVING COUNT(*) > 1) p
 ON(ss.name = p.name and tt.department = p.department)

Answer 2

select * from student left join SudentId_Subject on Student.SudentId = SudentId_Subject.DepId where Student.SudentId = SudentId_Subject.DepId