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;
Leave a Comment