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}
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; 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; 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` ); 