CODEWITHSUNDEEP
mysql
Ryan
Ryan

Reputation: 2886

Find duplicate records in table mysql

I have a table like the following:

| ID | Short Name | Long Name |
|----|------------|-----------|
| 1  | s1         | l2        |
| 2  | s1         | l2        |
| 3  | s1         | l2        |
| 4  | s5         | l6        |
| .. | ...        |           |

I want to get all records that share the same Short Name and Long Name. I need their shared short name, long name, and 3 duplicates' IDs. For this particular example, I want {s1, l2, 1,2,3}

Upvotes: 0

Views: 70

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can use exits to see if there are any other data exists with the same condition where ID is not same.

select t1.* from table_name t1 
where exists (
  select 1 from table_name t2 
  where 
  t1.ID <> t2.ID 
  and t1.`Short Name` = t2.`Short Name` 
  and t1.`Long Name` = t2.`Long Name`
);

Upvotes: 0

Shawn Lehner
Shawn Lehner

Reputation: 1323

This is a fairly simple problem to solve. Basically what you want to do is write a subquery that counts the number of rows that match on your specified field for each row in your query. I have included a few examples below.

Find all rows that are duplicates and match on both name fields

SELECT * FROM TableName WHERE (SELECT COUNT(*) FROM TableName AS T2 WHERE T2.ShortName = TableName.ShortName AND T2.LongName = TableName.LongName) > 1;

Find all rows that are duplicates and match on the short name

SELECT * FROM TableName WHERE (SELECT COUNT(*) FROM TableName AS T2 WHERE T2.ShortName = TableName.ShortName) > 1;

Find all rows that are duplicates and match on the long name

SELECT * FROM TableName WHERE (SELECT COUNT(*) FROM TableName AS T2 WHERE T2.LongName = TableName.LongName) > 1;

Upvotes: 1

Trinimon
Trinimon

Reputation: 13957

Simply use a a self join of your table and select those rows where the two names are equal and the ids differ:

SELECT * 
  FROM <table> t1, <table> t2
 WHERE t1.id        <> t2.id
   AND t1.short_name = t2.short_name
   AND t1.long_name  = t2.long_name;

Upvotes: 0

Related Questions