Find duplicate customer address in database

[♥14steve14]

Before I upload all my customer data to a new version of Phoenix, I am having a bit of a clear out of old and inactive customers. The inactive customers was easy as there was an addon ( I deleted over 800 customers who have never bought anything going back 5 years). Finding duplicate customers is proving to be a bit harder. Customers sometimes cant get the forgotten password link to do anything so create another account using a different email address, and its these that I want to find. Once found I have the addon to link both accounts together.

Does anyone know of an addon or a way to check database entries for the same 1st line of the address. Once I have the customers details I can manually check the rest is the same.

[♥JcMagpie]

This simple script will show all duplication

SELECT entry_street_address, 
 COUNT(entry_street_address) AS NumOccurrences
FROM address_book
GROUP BY entry_street_address
HAVING ( COUNT(entry_street_address) > 1 )

 

[♥14steve14]

Cheers Zahid that worked a treat. Not as many as I thought there would be. Most are where ustomers have duplicated addresses in their accounts.  There are only about 8 that actually have duplicate accounts. Thanks for your help.

[Jack_mcs]

@14steve14You could install Honeypot. It has an address checker tool in admin. It works without having to enable Honeypot, if that is wanted, and doesn't require any admin file changes to use in Phoenix.

[♥ecartz]

SELECT ab.entry_street_address, GROUP_CONCAT(ab.customers_id SEPARATOR ' ')
 FROM (SELECT customers_id, entry_street_address FROM `address_book` GROUP BY customers_id) ab
 GROUP BY ab.entry_street_address
 HAVING COUNT(ab.entry_street_address) > 1 

Might be a little easier to use.  It will skip over duplicated addresses in the address_book for one customer, but show duplicates across customers.  And it gives you the duplicate customer IDs rather than just the count and the duplicate address. 

[mhsuffolk]

@14steve14 My experience is that the street address entered by the customer can have spelling mistakes, some people don't even know where they live!

Suggest you run these excellent querys on the entry_postcode field instead. Customers tend to get their post code correct however some leave out the space before the last 3 characters which could be an issue. I believe there is an SQL query term "REPLACE" that would solve that problem but I am not sure how to implement it.

[♥14steve14]

1 hour ago, mhsuffolk said:

@14steve14 My experience is that the street address entered by the customer can have spelling mistakes, some people don't even know where they live!

Suggest you run these excellent querys on the entry_postcode field instead. Customers tend to get their post code correct however some leave out the space before the last 3 characters which could be an issue. I believe there is an SQL query term "REPLACE" that would solve that problem but I am not sure how to implement it.

I will try using the postcode in the query but I think it may throw some weird results as I know I have some customers who are neighbours. I will let you know how I get on so thanks for the suggestion.