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

Question

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

Answer 1

One way

select t1.* from Table t1
join(
select username from Table
group by username
having count(username) >1) t2 on t1.username = t2.username

Answer 2

The simplest way I can think of to do this uses a sub-query:

select * from username un1 where exists
(select null from username un2 
where un1.user_name = un2.user_name and un1.id <> un2.id);

Answer 3

The sub-query selects all names that have >1 row with that name... outer query selects all the rows matching those IDs.

SELECT T.* 
FROM T
    , (SELECT   Dupe_candidates.USERNAME
       FROM     T AS Dupe_candidates
       GROUP BY Dupe_candidates.USERNAME
       HAVING   count(*)>1
     ) Dupes
WHERE T.USERNAME=Dupes.USERNAME

Answer 4

You can try the following:

SELECT * 
FROM dbo.Person as p1 
WHERE 
(SELECT COUNT(*) FROM dbo.Person AS p2 WHERE p2.UserName = p1.UserName) > 1;