Untitled

SELECT 
    u1.username AS original_username,
    u1.firstname,
    u1.lastname,
    u1.manager,
    u1.siteid,
    GROUP_CONCAT(u2.username SEPARATOR ', ') AS duplicate_usernames
FROM 
    users u1
JOIN 
    users u2
ON 
    (
        -- Criterion 1: firstname + lastname + manager + siteid
        (u1.firstname = u2.firstname AND 
         u1.lastname = u2.lastname AND 
         u1.manager = u2.manager AND 
         u1.siteid = u2.siteid)
        OR
        -- Criterion 2: firstname + lastname + siteid
        (u1.firstname = u2.firstname AND 
         u1.lastname = u2.lastname AND 
         u1.siteid = u2.siteid)
        OR
        -- Criterion 3: lastname + firstname + siteid
        (u1.lastname = u2.lastname AND 
         u1.firstname = u2.firstname AND 
         u1.siteid = u2.siteid)
        OR
        -- Criterion 4: firstname + lastname + siteid without spaces
        (REPLACE(CONCAT(u1.firstname, u1.lastname, u1.siteid), ' ', '') =
         REPLACE(CONCAT(u2.firstname, u2.lastname, u2.siteid), ' ', ''))
    )
WHERE 
    u1.username != u2.username
    AND u1.statuskey = 1
    AND u2.statuskey = 1
GROUP BY 
    u1.username, u1.firstname, u1.lastname, u1.manager, u1.siteid
ORDER BY 
    u1.username;