Dennis Dennis -4 years ago 119
MySQL Question

MYSQL Duplicate entries

i am trying to find duplicate entries within my mysql table. I would like to compare the different fields with each other. Here is the structure of my table:

ID FirstName LastName Street ZIP City IpAddress
1 Jack Smith 2nd 12345 Sample1 12.21.24.212
2 Paul Miller 3rd 45685 Sample2 78.54.85.654
3 Jenny Smith 3rd 77273 Sample3 84.91.67.311
4 Frank Jackson 1st 27819 Sample1 78.54.85.654
5 Jack Smith 3rd 72891 Sample2 94.79.99.465


Now i would like to compare the street and ip column individually and then i would like to find the combination of the first- and lastname. There are actually a few more columns in my table that i would like to search for but i think my example above should give you an idea about what i am planning.

I need the id numbers of the entries that could potencially duplicates.

In the example above the output should be the id numbers 1 and 5 when i compare the combination of the first- and lastname.

The output should be the id numbers 2,3 and 5 if i compare the street names.

And the output for the ip addresses should be id numbers 2 and 4.

Does anyone have some ideas about how i should do this? What is the best way to compare those different tables? I don't mind if i have to do several queries.

Answer Source

Use GROUP_CONCAT() to get all the IDs within a group, and GROUP BY to specify the columns that you're looking for duplidates of. And you can use COUNT(*) so you only return the ones that have duplicates.

For streets:

SELECT street, GROUP_CONCAT(id)
FROM yourTable
GROUP BY street
HAVING COUNT(*) > 1

For names:

SELECT firstname, lastname, GROUP_CONCAT(id)
FROM yourTable
GROUP BY firstname, lastname
HAVING COUNT(*) > 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download