I have two tables that (simplified) look like this:
customers
=========
customer_id [integer, autoincrement]
email [varchar]
memos
=====
memo_id [integer, autoincrement]
customer_id [integer]
memo_text [varchar]
There are some duplicate email addresses in the "customers" table. A
customer might have any number of memos.
What I want to do is locate all of the duplicate email addresses and their
associated memos (so that we can try to decide which to keep).
If I don't try to handle the memos, I can easily get a list of duplicated
emails:
SELECT customers.email, COUNT(*) AS n
FROM customers
GROUP BY customers.email
HAVING n > 1;
That works fine: I get a list of all of the duplicated email addresses, and
the number of each.
The problem starts when I try to fetch the memos. I want one email address,
two or more customer_ids (so I can tell them apart), and all of the memos
for each customer_id (preferably using GROUP_CONCAT(). In other words, this
is what I want:
[EMAIL PROTECTED] fred_id_1 fred_memos_1
fred_id_2 fred_memos_2
fred_id_3 fred_memos_3
[EMAIL PROTECTED] john_id_1 john_memos_1
john_id_2 john_memos_2
No matter how I try, I always wind up with something like this:
[EMAIL PROTECTED] fred_id_1 fred_memos_1
[EMAIL PROTECTED] john_id_1 john_memos_1
I can't seem to avoid collapsing duplicate email records into one; that is,
I only see the the first occurrence of the email (which is what I want), the
first customer_id for that email address, and the first set of memos for
that email address.
If I don't group, then I can get every record; but then I don't see how I
can pick off only the email addresses that are in the customers table more
than once.
I might be missing the obvious here. I've fiddled with sub-queries, but
didn't really get anywhere.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
