MySQL: Finding repeated names in my User table

Question

I want to find all users whose name appears at least twice in my User table. 'email' is a unique field, but the combination of 'firstName' and 'lastName' is not necessarily unique.

So far I have come up with the following query, which is very slow, and I am not even sure it is correct. Please let me know a better way to rewrite this.

SELECT CONCAT(u2.firstName, u2.lastName) AS fullName
FROM cpnc_User u2
WHERE CONCAT(u2.firstName, u2.lastName) IN (

SELECT CONCAT(u2.firstName, u2.lastName) AS fullNm
FROM cpnc_User u1
GROUP BY fullNm
HAVING COUNT(*) > 1

)

Also, note that the above returns the list of names that appear at least twice (I think so, anyway), but what I really want is the complete list of all user 'id' fields for these names. So each name, since it appears at least twice, will be associated with at least two primary key 'id' fields.

Thanks for any help! Jonah

Answer 1

SELECT u.*
FROM cpnc_User u JOIN
(
    SELECT firstName, lastName
    FROM cpnc_User
    GROUP BY firstName, lastName
    HAVING COUNT(*) > 1
) X on X.firstName = u.firstName AND x.lastName = u.lastName
ORDER BY u.firstName, u.lastName

There is no need to make up a concatenated field, just use the 2 fields separately

Answer 2

SELECT u.id, u.firstName, u.lastName
FROM cpnc_User u, (
  SELECT uc.firstName, uc.lastName 
  FROM cpnc_User uc 
  GROUP BY uc.firstName, uc.lastName 
  HAVING count(*) > 1
) u2
WHERE (
  u.firstName = u2.firstName
  AND u.lastName = u2.lastName
)