MySQL Locate Duplicate Records

Question

I'm attempting to locate all records in a table that have duplicate data in several fields, and return all fields for those duplicate records. I've researched this issue on this site and found similar issues, but none that seemed to address my particular one.

For example, these would be duplicate records:

first_name last_name state
Bob Jones CA
Bob Jones CA

While these would not be:

first_name last_name state
Bob Jones CA
Bob Jones CO

Nor these:

first_name last_name state
Bob Jones CA
Bob Smith CA

Nor these:

first_name last_name state
Bob Jones CA
Jim Jones CA

It may be easier if I show the MySQL statement that I've attempted:

SELECT * 
FROM table_1 
WHERE 
    gy >= 2 AND arc = '' AND   
    first_name IN (
        SELECT first_name FROM table_1 
        GROUP BY first_name HAVING count(first_name) >1
    ) AND   
    last_name IN (
        SELECT last_name FROM table_1 
        GROUP BY last_name HAVING count(last_name) >1
    ) AND  
    state IN (
        SELECT state FROM table_1 
        GROUP BY state HAVING count(state) >1
    )

This statement appears to return every record in the table that meets the first two criteria (gy >= 2 AND arc = '') and ignores the final three, which I don't understand.

Does anyone have a recommendation on a more successful way to return all fields for all records that have duplicate values in all three of the first_name, last_name and state fields?

Answer 1

You can try something like this:

SELECT    First_name, Last_name, State
FROM      Table_1
GROUP BY  First_name, Last_name, State
HAVING    COUNT(1) > 1

This would only return duplicates on all three fields. Is that what you are looking for?

EDIT:

To get each record of the duplicate, you can use the above as a subquery for selecting duplicates:

SELECT  *
FROM    dbo.SO2 a
WHERE   EXISTS (
            SELECT    First_name, Last_name, State
            FROM      dbo.SO2 b
            GROUP BY  First_name, Last_name, State
            HAVING    COUNT(1) > 1
                        AND a.First_name = b.First_name
                        AND a.Last_name = b.Last_name
                        AND a.State = b.State)