SEO

I need to find records in a table that may be duplicate records.

The table stores basic information about the Users. I want to do a
match on the FName and LName fields. The query I have looks like this....

SELECT u1.UserID, u1.FName, u1.LName, u1.Email, COUNT(u1.Email) AS `Count`
FROM user u1
JOIN user u2 ON u1.FName = u2.FName AND u1.LName = u2.LName
GROUP BY u1.UserID
HAVING Count > 1
ORDER BY u1.LName, u1.FName

This works fine. However, I would like to help determine which of the
duplicates should be removed buy getting data from another table. That
is were I get lost.

The second table has "User Profile" records for each user. It is a one
to many relationship so each user can have 0 or more profile records.
What I would like to add to this query is a count of how many profile
records each UserID has in the User profile table. That way if there
are two users with the same name and one has 5 profile records and the
second has no profile records it is clear which to remove. What I have
come up with is....

SELECT u1.UserID, u1.FName, u1.LName, u1.Email, COUNT(u1.Email) AS
`Count`, COUNT(p.UserID) as ProfileCount
FROM user u1
JOIN user u2 ON u1.FName = u2.FName AND u1.LName = u2.LName
LEFT OUTER JOIN userprofile p ON u1.UserID = p.UserID
GROUP BY u1.UserID
HAVING `Count` > 1
ORDER BY u1.LName, u1.FName

However that returns rows that are not duplicate names and I'm not sure why.

--
Chris W
KE5GIX

"Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm"