Reputation: 21249
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
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
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
Reputation: 86902
Select
*
FROM UserTable ut
JOIN UserTable ut2 on ut2.firstname = ut.lastname and ut2.lastname = ut.firstname
Upvotes: 2