Subject: Join to find Duplicates 2007-10-16 - By Chris W
Back
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"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
