How do I return different entries which share the same ID in the same table?

Question

I have a legacy table: Students in my database (SQL-Server) as below:

STUDENTID - SURNAME - FORENAME 
 DJ1           Doe       John
 DJ1           Doe       John
 DJ2           Doe       Jane
 DJ2           Doe       Jane
 DJ1           Dark      Odin
 AS1           Tom       Layton
 ....

It is expected to have duplication in my table like in the case of John Doe or Jane Doe. I don't want that to be treated as a duplicate data. What I want to avoid is actually having different student sharing the same STUDENTID (e.g. Odin Dark and John Doe). How do I create a query that return the student with different name sharing the same STUDENTID?

So My Query will return something like this:

ID  - Surname - Forname
DJ1    Doe      John
DJ1    Dark     Odin

Thanks in advance.

http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table — Jacob H, May 16 '17 at 13:47
@JacobH why don't you suggest that this is a duplicate, and post your link as an answer? — Eli, May 16 '17 at 13:49
Possible duplicate of [Finding duplicate values in a SQL table](http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Jacob H, May 16 '17 at 13:50
Expecting to have duplicates in a table is a big red flag that something is horribly wrong with the design. The whole point of relational data is to avoid duplication. — Sean Lange, May 16 '17 at 13:50
Yes you should have a true identity column in the table so you can store unique records, and whatever is generating your "StudentID" (not actually an ID) should at least check the existing values and use the next number in sequence. — Jacob H, May 16 '17 at 13:53
that's the issue I have now. At the moment it's a legacy DB, so I can't do much about it. The duplicate data is fine. but my concern is that students sharing the same ID... How do I actually find those students.. — Saber Alex, May 16 '17 at 13:56
@JacobH, I think this is not a duplicate. First of all, I'm fine of having the same students (i.e. in the case of John Doe) in my table. — Saber Alex, May 16 '17 at 13:59
It is definitely a duplicate of a question asking how to find duplicates. A duplicate duplicate so to speak. — Jacob H, May 16 '17 at 14:00
@JacobH, Nope. In the link you give me, the Jane Doe (a new data that I just added) will be included in the solution that they propose. I don't want that. — Saber Alex, May 16 '17 at 14:09

score 0 · Answer 1 · answered May 16 '17 at 14:29

Here you go. Not the prettiest query but it gets you the data you are looking for I think.

SELECT * FROM Students s
JOIN 
(SELECT StudentID, Surname, Forename FROM Students
GROUP BY StudentID, Surname, Forename
HAVING COUNT(*) > 1) sub 
ON sub.StudentID = s.StudentID
WHERE sub.Forename <> s.Forename and sub.Surname <> s.Surname and sub.StudentID = s.StudentID

score 0 · Answer 2 · answered May 16 '17 at 14:33

different names with the same id can be found with this query. It will not show the duplicate records.

select distinct s1.* 
from students s1
join students s2 on s1.studentid = s2.studentid and (s1.surname <> s2.surname or s1.forename <> s2.forename)

How do I return different entries which share the same ID in the same table?

2 Answers2