CODEWITHSUNDEEP
mysqlduplicates
Ben
Ben

Reputation: 21249

mysql - Find duplicate users with firstname/lastname swapped

I need to find users that have been inserted twice in a table, but with their first name & last name swapped.

e.g. Bob Smith is in the database as 

firstname:Bob 
lastname:Smith
&
firstname:Smith
lastname:Bob

What's the best query to find those users?

The server runs mysql.

Upvotes: 0

Views: 1352

Answers (3)

gbn
gbn

Reputation: 432712

SELECT
    firstname, lastname
FROM
    (
    SELECT firstname, lastname FROM MyTable -- WHERE firstname <> lastname
    UNION ALL
    SELECT lastname, firstname FROM MyTable -- WHERE firstname <> lastname
    ) foo
GROUP BY
   firstname, lastname
HAVING 
   COUNT(*) > 1

Upvotes: 2

Marc B
Marc B

Reputation: 360912

SELECT orig.firstname, orig.lastname
FROM yourtable AS orig
INNER JOIN yourtable AS dupes ON orig.firstname = dupe.lastname AND orig.lastname = dupe.firstname

Basically, do a self-join on the user table, but only on the records where the fn/ln dupe-swap occurs.

Upvotes: 1

John Hartsock
John Hartsock

Reputation: 86902

Select 
  *
FROM UserTable ut
JOIN UserTable ut2 on ut2.firstname = ut.lastname and ut2.lastname = ut.firstname

Upvotes: 2

Related Questions