April 11, 2004 at 11:27 pm
I have a simple table structure that has customers in one table and addresses in another. The addresses table is linked to customers by a customerid value. The relevant fields are as follows:
Customers.ID
Customers.LastName
Customers.Phone
Addresses.Street (which is just the street name like "main")
What I want to do is find duplicate customer accounts where the Phone, LasteName and Street all match but the customers.id do not match.
With this information, how would I write the query to find this information?
Thanks!
Mike
April 12, 2004 at 12:24 pm
This will get you a list of those customers. You could put this into a view and then query off of it.
SELECT
COUNT(c.ID) AS DuplicateCount,
c.LastName,
c.Phone,
a.Street
FROM
Customers c INNER JOIN Addresses a ON
c.ID = a.CustomerID
GROUP BY
c.LastName,
c.Phone,
a.Street
HAVING
COUNT(c.ID) > 1
April 12, 2004 at 7:34 pm
the is fine. But may be you want to know even the customerids also who are duplicates. Then try the following
select c.id,c.lastname,c.phone,c.street from customers c inner join addresses a on c.id=a.customerid where exists
(select 0 from c1 inner join addresses a1 on c1.id=a1.customerid where c1.lastname=c.lastname and c1.phone=c.phone and a1.street=a.street and c.id<>c1.id)
This query will foind out the duplicates with customerids also and I think performence of this querry would also be efficient. If I am incorrect please mail me to kalyanow@yahoo.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply