CODEWITHSUNDEEP
mysql
user4440261
user4440261

Reputation: 23

Query to return all duplicates for all rows

I want to return each row in the table, along with the id of each other row that has a duplicate value in a specific column. I can get all duplicates easily enough, but I need to return more data

Example Data:

--------------------------------
| Firstname   | Lastname  | ID |
--------------------------------
| John        | Smith     |  1 |
--------------------------------
| Jane        | Smith     |  2 |
--------------------------------
| Bill        | Smith     |  3 |
--------------------------------

If I match on Lastname, I would like to return

ID    Firstname   Lastname   dup_Fname  dup_Lname dup_id
1     John        Smith      Jane       Smith     2
1     John        Smith      Bill       Smith     3
2     Jane        Smith      John       Smith     1
2     Jane        Smith      Bill       Smith     3
3     Bill        Smith      John       Smith     1
2     Bill        Smith      Jane       Smith     2

I really only need to return IDs, but included the names in the example for clarity

Upvotes: 2

Views: 26

Answers (1)

Raging Bull
Raging Bull

Reputation: 18767

You can do this by joining the table with itself using different alias names:

SELECT T1.ID, T1.FirstName, T1.LastName,
       T2.FirstName as dup_FName, T2.LastName as dup_LName, T2.ID as dup_id
FROM TableName T1, TableName T2
WHERE T1.ID <> T2.ID
ORDER BY T1.ID,T2.ID

Result:

ID  FIRSTNAME   LASTNAME    DUP_FNAME   DUP_LNAME   DUP_ID
1   John        Smith       Jane        Smith       2
1   John        Smith       Bill        Smith       3
2   Jane        Smith       John        Smith       1
2   Jane        Smith       Bill        Smith       3
3   Bill        Smith       John        Smith       1
3   Bill        Smith       Jane        Smith       2

See result in SQL Fiddle.

Upvotes: 1

Related Questions