I'm trying to find each row with duplicates in a MySQL database.
1 alex smith 2 bob smith 3 alex smith
I want to return:
1 alex smith 3 alex smith
This code will find duplicates, but it doesn't list each row that is a duplicate.
SELECT *, CONCAT(`firstName`, ' ', `lastName`) as full_name, COUNT(*) d FROM users GROUP BY full_name HAVING d > 1;
The code below is what I'm trying to get each row that is a dupliate, but I get the error "#1054 - Unknown column 'full_name' in 'IN/ALL/ANY subquery'"
SELECT CONCAT(`firstName`, ' ', `lastName`) as full_name FROM users WHERE full_name IN ( SELECT CONCAT(`firstName`, ' ', `lastName`) as full_name FROM users GROUP BY full_name HAVING COUNT(full_name) > 1 )Answer1:
You cannot use aliases in WHERE clauses; and you do not actually need the CONCAT <em>(it increases your computational costs in this case)</em>; instead you can compare "tuples".
SELECT * FROM users WHERE (`firstName`, `lastName`) IN ( SELECT `firstName`, `lastName` FROM users GROUP BY `firstName`, `lastName` HAVING COUNT(*) > 1 );