First the table layouts... somewhat trimmed...
mysql> desc act.dme_orders;
+------------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------------+------+-----+---------+----------------+
| id | int(10) unsigned zerofill | NO | PRI | NULL | auto_increment |
| act_id | varchar(12) | YES | | NULL | |
...
| pat_acct | varchar(50) | YES | | NULL | |
| firstname | varchar(50) | YES | MUL | NULL | |
| lastname | varchar(50) | YES | MUL | NULL | |
| order_numb | varchar(8) | YES | | NULL | |
...
+------------+---------------------------+------+-----+---------+----------------+
22 rows in set (0.01 sec)
===============================================
mysql> desc medact.patdata;
+------------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------------+------+-----+---------+----------------+
| id | int(10) unsigned zerofill | NO | PRI | NULL | auto_increment |
| pat_lname | varchar(20) | NO | MUL | | |
| pat_fname | varchar(12) | NO | MUL | | |
...
+------------+---------------------------+------+-----+---------+----------------+
81 rows in set (0.01 sec)
================================================
This select gives me all records in patdata that are duplicates on the combination of first and last name:
select
concat_ws(" ",medact.patdata.pat_fname,medact.patdata.pat_lname) as bla,
count(concat_ws(" ",medact.patdata.pat_fname,medact.patdata.pat_lname)) as name_cnt
from patdata
group by bla
having name_cnt >1;
I have two tables as listed. One is a customer table. The other is an order table. Unfortunately the order table was not connected to the customer table via an account number or rowID. (This is a legacy system).
The orders do have customer first and last name however. Most of these match up to customers. However there are customers with the same First and Last name. (755 to be exact).
Also there are often multiple orders for a customer. Thus in the order table we might have names like this:
Tom Jones order1
Tom Jones order2
Tom Jones order1 (a second tom Jones)
Bill Smith order1
Sue Smith order1
Whereas in the customer table we might have something like this:
Tom Jones account2i9399
Tom Jones account9928
Bill Smith account3332
Bill Smith account0092
Sue Smith account938881
Thus you see we have only one sue smith.
What I would like to do is a select where I eliminate the dups from the order table, then find any dups on the remaining names from the customer table...perhaps something like this if it were multiple sql statements
select distinct(concat_ws(" ",firstname,lastname)) from act.dme_orders;
Then on the results this:
select
concat_ws(" ",medact.patdata.pat_fname,medact.patdata.pat_lname) as bla,
count(concat_ws(" ",medact.patdata.pat_fname,medact.patdata.pat_lname)) as name_cnt
from patdata
group by bla
having name_cnt >1
where these names are taken from the preceding list....;