Find and group duplicates

Question

Hopefully I'm able to explain what I'm trying to achieve, it's a bit complicated I think.

I have two tables like this:

ID | Names -------------- A | Name1 B | Name2 C | Name3 ID | md5s -------------- A | a A | b B | c C | a C | c

I'm trying to achieve this: In the end I want to have a list of all "Names" that have duplicate MD5 values and in which other "Names" these MD5 values were found.

So I want to get something like this:

Name1 has 5 duplicate entries in "md5s" with Name8, 4 with Name10 ...

I need a list for all "Names" like described above.

Hopefully that makes sense to someone. :)

I already tried it with this SQL statement:

SELECT names,COUNT(names) AS Num FROM tablename GROUP BY names HAVING(Num > 1);

But that gives me only the md5s that are duplicates. The relation to the rest is totally missing.

*edit:fixed typo

David Marx · Accepted Answer · 2011-07-08 16:40:57Z

I feel like there must be a better solution than this, but here's what I've thrown together for you:

SELECT a.names NAME, b.names DUPE_NAME, COUNT(*) NUM_DUPES FROM names_tbl a, names_tbl b, md5_tbl md5a, md5_tbl md5b WHERE a.id < b.id AND a.id = md5a.id AND b.id = md5b.id AND md5a.md5 = md5b.md5 GROUP BY a.names, b.names ORDER BY a.names

The rule of thumb with finding duplicates is that you probably need to do a self join. This would be simpler if the names and their associated md5's were in the same record, but because they're in separate tables I think you need two versions of each table.

Hi David, great this seems to work, thank you. There is only one last "problem". Each "Resultgroup" appears two times. In the end we have A - B = 11 and B - A = 11. Is there any possibility to fix this with SQL or do i have to handle this in my script?
Hi Andy, fixed the script. Instead of joining where the a.id <> b.id, I modified it to a.id < b.id so it will only return one record for each matched pair. Let me know if you have any other questions.

Collectives™ on Stack Overflow

Find and group duplicates

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related