Untitled

SELECT 
    u1.username AS original_username,
    u1.firstname,
    u1.lastname,
    u1.manager,
    u1.siteid,
    u2.username AS duplicate_username
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
ORDER BY 
    u1.username, duplicate_username;