Find Duplicate Customers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find Duplicate Customers

Author

Topic

fawadafr
Starting Member

47 Posts

Posted - 2010-05-12 : 12:26:35

I have written the following SQL statement to find all duplicate customers who have exact same name, address, phone, etc. But, it returns all the customers not just the duplicates:


SELECT  c1.CustomerCode
	,c1.CustomerName
	,c1.Telephone
	,c1.Email
	,c2.CustomerCode
	,c2.CustomerName
	,c2.Telephone
	,c2.Email
FROM Customer AS c1
JOIN Customer AS c2
ON c1.CustomerCode = c2.CustomerCode
WHERE c1.CustomerName = c2.CustomerName AND c1.Address = c2.Address AND c1.City = c2.City

Could you please provide me with your suggestions?

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:32:45

[code]
SELECT c1.CustomerCode
,c1.CustomerName
,c1.Telephone
,c1.Email
FROM Customer AS c1
JOIN (SELECT CustomerCode,Address,City FROM Customer GROUP BY CustomerName, Address,City HAVING COUNT(*) > 1)AS c2
ON c1.CustomerCode = c2.CustomerCode
AND c1.Address = c2.Address
AND c1.City = c2.City
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2010-05-12 : 12:47:32

Thanks Visakh. I get the following error message when I execute your statement:

Msg 8120, Level 16, State 1, Line 1
Column 'Customer.CustomerCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:54:18

it was a typo


SELECT  c1.CustomerCode
	,c1.CustomerName
	,c1.Telephone
	,c1.Email
FROM Customer AS c1
JOIN (SELECT CustomerCode,Address,City FROM Customer GROUP BY CustomerCode, Address,City  HAVING COUNT(*) > 1)AS c2
ON c1.CustomerCode = c2.CustomerCode
AND c1.Address = c2.Address 
AND c1.City = c2.City

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2010-05-13 : 11:50:33

I came up with the following script and it works great:


SELECT   c1.CustomerCode
	,c1.CustomerName
	,c1.Telephone
	,c1.Email
	,c1.DefaultContact
	,c1.AssignedTo
	,c1.cust_id_with_webstore_C AS VolusionCustID
	,c2.CustomerCode
	,c2.CustomerName
	,c2.Telephone
	,c2.Email
	,c2.DefaultContact
	,c2.AssignedTo
	,c2.cust_id_with_webstore_C AS VolusionCustID
FROM Customer AS c1
JOIN Customer AS c2
ON c1.CustomerCode <> c2.CustomerCode
WHERE c1.CustomerName = c2.CustomerName AND 
      c1.Address = c2.Address AND 
      c1.City = c2.City

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 12:37:55

quote:

Originally posted by fawadafr

I came up with the following script and it works great:


SELECT   c1.CustomerCode
	,c1.CustomerName
	,c1.Telephone
	,c1.Email
	,c1.DefaultContact
	,c1.AssignedTo
	,c1.cust_id_with_webstore_C AS VolusionCustID
	,c2.CustomerCode
	,c2.CustomerName
	,c2.Telephone
	,c2.Email
	,c2.DefaultContact
	,c2.AssignedTo
	,c2.cust_id_with_webstore_C AS VolusionCustID
FROM Customer AS c1
JOIN Customer AS c2
ON c1.CustomerCode <> c2.CustomerCode
WHERE c1.CustomerName = c2.CustomerName AND 
      c1.Address = c2.Address AND 
      c1.City = c2.City

--
Fawad Rashidi
Web Developer
www.fawadafr.com

check this and see why it might not be a good method

http://www.sqlservercentral.com/articles/T-SQL/61539/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2010-05-13 : 14:44:20

Thanks for sharing... very interesting article.

--
Fawad Rashidi
Web Developer
www.fawadafr.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources