MySQL, return only rows where there are duplicates among two columns.

Question

I have a table in MySQL of contact information ;

first name, last name, address, etc.

I would like to run a query on this table that will return only rows with first and last name combinations which appear in the table more than once.

I do not want to group the "duplicates" (which may only be duplicates of the first and last name, but not other information like address or birthdate) -

I want to return all the "duplicate" rows so I can look over the results and determine if they are dupes or not. This seemed like it would be a simple thing to do, but it has not been.

Every solution I can find either groups the dupes and gives me a count only (which is not useful for what I need to do with the results) or doesn't work at all.

Is this kind of logic even possible in a query ? Should I try and do this in Python or something?

Answer 1

You should be able doing this with the GROUP BY approach in a sub-query.

SELECT t.first_name, t.last_name, t.address
FROM your_table t
JOIN ( SELECT first_name, last_name
       FROM your_table
       GROUP BY first_name, last_name
       HAVING COUNT(*) > 1
     ) t2
  ON ( t.first_name = t2.first_name, t.last_name = t2.last_name )

The sub-query returns all names (first_name and last_name) that exist more than once, and the JOIN returns all records that match these names.

Answer 2

You could do it with a GROUP BY / HAVING and A SUB SELECT. Something like

SELECT  t.*
FROM    Table t INNER JOIN
        (
            SELECT  FirstName, LastName
            FROM    Table 
            GROUP BY FirstName, LastName
            HAVING COUNT(*) > 1
        ) Dups ON t.FirstName = Dups.FirstName
                AND t.LastName = Dups.LastName

Answer 3

select * from people
join (select firstName, lastName
  from people
  group by firstName, lastName
  having count(*) > 1
) dupe
using (firstName, lastName)