I know this is php but I didn't find a place on the mysql forum for help of this sort and you guys have been great in the past so I'm hoping I'm not overstepping here. Thanks in advance for the help.
First some background I'm trying to locate duplicates of customers in a table cust
, but its more difficult than that. There are multiple 'shops' that use the file and one customer can be used by more than one 'shop' or not. There is a second table bonus
that attaches the customers to the shops and holds some other info that is specific to that shop and customer. The two tables are related with the customer id. cust
.id
and bonus
.custid
the id is a hash of the first three letters of the last name + first four letters of the first name + a number if there is already one These are unique id's but sometimes a person will sign up with two emails or a new address and won't acknowledge that it found a previous version or whatever. At any rate sometimes I end up with two records for the same individual. So the idea I'm trying to do with this select is to pull all of the customers that have more than one record in the cust
(first 7 characters the same) but also that are attached to the shop that the query is coming from (shop that is logged in) so I don't share customers with other shops that aren't actually shared. So here is the query made from shop 1
SELECT `cust`.* ,`bonus`.`password` from `cust`,`bonus`
WHERE `cust`.`id`=`bonus`.`custid` and `bonus`.`shop`='1' and substr(`cust`.`id`,1,7)
IN (SELECT substr(`bonus`.`custid`,1,7) FROM `bonus` GROUP BY substr(`bonus`.`custid`,1,7)
HAVING COUNT(*)>1)
ORDER BY `bonus`.`custid`
I thought I was limiting it to shop one in the original where statement, but when the same customer is attached to more than one shop it pulls them up even though there is only one entry in the cust
file. What am I doing wrong?