We help IT Professionals succeed at work.

T-SQL How To: Compare and List Duplicate Entries in a Table

SQL Server 2000.  Single table has a list of users that includes a unique user ID and a non-unique user name.

I want to search the table and list out any users that share the same non-unique user name.  For example, my table looks like this:

ID   User Name   Name
==   =========   ====
0    parker      Peter Parker
1    parker      Mary Jane Parker      
2    heroman     Joseph (Joey) Carter Jones
3    thehulk     Bruce Banner

What I want to do is do a SELECT and have the result set be:

ID   User Name   Name
==   =========   ====
0    parker      Peter Parker
1    parker      Mary Jane Parker      

from my table.

I'm not a T-SQL guru.  I can do the basic joins and such, but I'm thinking there must be an elegant way of doing this.  Barring elegance, there must be ANY way of doing this.

I appreciate any methods that you can help me with on this topic.  Thanks!
---Dan---
Comment
Watch Question

Andyc75Software Architect
Top Expert 2010

Commented:
Give this a try


select A.* from same_users A
   inner join same_users B on A.user_name = B.user_name and A.id <> B.id

Open in new window

SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
Commented:
This should do:

SELECT ID, [User Name], Name
FROM ur_table
WHERE [User Name] in (
SELECT [User Name]
FROM ur_table
GROUP BY [User Name]
HAVING count(ID) > 1)
Commented:
-- this will select all the records that have more then 1 UserName
Select *
From Table
Where UserName IN (
  Select UserName From Table
  Group By UserName
  Having Count(*) > 1
)
Andyc75Software Architect
Top Expert 2010
Commented:
Slight correction to my script (I forgot the group by)




select A.* from same_users A
   inner join same_users B on A.user_name = B.user_name and A.id <> B.id
group by A.id, A.user_name, A.name

Open in new window