Burak Topal Burak Topal - 1 month ago 6
MySQL Question

Select duplicate users in SQL Database

Hey there I searched a lot but cant find a answer.

SELECT * FROM `user` WHERE `user.originalID` != `user.fakeID`


this is only an example but all 2 id´s are in the same table cant find a solution seems easy but stress a lot :(

lets say we know that the guy with id 1 have an 2nd accout with id 9000 so i give him the fake id 9000 to identify him. Now I want to list all users on my list without the fake ones any one know a solution ?

orginalID fakeID firstName lastName
1 900 max muster
2 null John Html
900 null max muster


SO i wannt to list all user but only the Orginal ones. All fake ones should not be listet.

Answer

Normally a simple LEFT OUTER JOIN, and then check for no match

SELECT u1.* 
FROM user u1
LEFT OUTER JOIN user u2
ON u1.originalID = u2.fakeID
WHERE u2.fakeID IS NULL