CODEWITHSUNDEEP
mysqlsqlduplicates
dlofrodloh
dlofrodloh

Reputation: 1744

Find duplicate records based on two fields

I'm trying to modify the accepted answer in this question: Find duplicate records in MySQL in order to find duplicate records in my table based on two fields, Firstname and Lastname.

This is the original query which works fine and finds duplicate records based on Lastname...

SELECT Firstname, candidate.Lastname FROM candidate
INNER JOIN (SELECT Lastname FROM candidate
GROUP BY Lastname HAVING count(UserID) > 1) dup ON 
candidate.Lastname = dup.Lastname ORDER BY Lastname

Now I want to add Firstname in there as well... but I'm not really sure what I'm doing, the following doesn't work:

SELECT candidate.Firstname, candidate.Lastname FROM candidate
INNER JOIN (SELECT Firstname AND Lastname FROM candidate
GROUP BY Lastname HAVING count(UserID) > 1) dup ON 
candidate.Lastname = dup.Lastname AND candidate.Firstname = dup.Firstname 
ORDER BY Lastname

Upvotes: 0

Views: 94

Answers (1)

jpw
jpw

Reputation: 44921

Looks like you just got the syntax a bit wrong in the sub-query, maybe this is what you want?

SELECT candidate.Firstname, candidate.Lastname 
FROM candidate
INNER JOIN (
    SELECT Firstname, Lastname 
    FROM candidate
    GROUP BY Firstname, Lastname 
    HAVING COUNT(UserID) > 1
    ) dup 
    ON candidate.Lastname = dup.Lastname AND candidate.Firstname = dup.Firstname
ORDER BY Lastname

Upvotes: 2

Related Questions