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