Toogit Social Post

Din Shah

one year ago
hi,

Could you please help me to search for duplicate records across all tables in a MySQL database?
There are multiple tables in a database. Tables contain 10 columns including username VARCHAR), dateofbirth (VARCHAR) and pincode (INT)

I need a query that will do the following:-

Find records (matched on username and dateofbirth) that appear 2 times or more across all of the tables AND where pincode <> "10001"
2 Likes
1 comments, 0 Followers

Try below:

select username, dateofbirth, pincode
from (
select username, dateofbirth, pincode from table1 where pincode <> 1
union all
select username, dateofbirth, pincode from table2 where pincode <> 1
union all
...
union all
select username, dateofbirth, pincode from table10 where pincode <> 1
) x
group by username, dateofbirth, pincode
having count(1) >= 2

by Greg Costigen one year ago