Finding duplicates across tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Reviresco
Forum Contributor
Posts: 172
Joined: Tue Feb 19, 2008 4:18 pm
Location: Milwaukee

Finding duplicates across tables

Post by Reviresco »

I have 75 MySQL tables with identical structure, from various email signup forms. Many of them duplicate data from other tables -- there are 70,000 email addresses, but only about 21,000 different ones.

I only really care about the "email" field. How can I most efficiently (using PHP) determine which tables have the same emails?

For example, I want to find that "something@example.com" is in Table 1, Table 6, Table 17, and Table 23.

I've already created a master list with no duplicates, but I need to find out where the redundancy is and clean it up.

(And for the record, I didn't create this mess. The client is coming to us from another company.)
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Finding duplicates across tables

Post by alex.barylski »

You might use UNION and the code from this blog:

http://www.petefreitag.com/item/169.cfm

Cheers,
Alex
Post Reply