Duplicate records Query

[Cynthia]

Can anyone help me to write a query to show customers who have duplicate accounts with Email address, first name, and last name. this is the table structure is Customer table

customerid(PK)
accountno
fname
lname


Records will be

like this

customerid accountno fname lastname
1 2 lori taylor
2 2 lori taylor
3 1 randy dave


Email

emailid (PK)
customerid
emailaddress

[Todd]

How about this one?

SELECT Email,FirstName,LastName
FROM Email A
JOIN Customer B
ON A.Customerid = B.Customerid
GROUP BY Email,FirstName,LastName
HAVING Count(*) > 1


------------
Cynthia at 7/9/01 3:48:30 PM

Can anyone help me to write a query to show customers who have duplicate accounts with Email address, first name, and last name. this is the table structure is Customer table

customerid(PK)
accountno
fname
lname


Records will be

like this

customerid accountno fname lastname
1 2 lori taylor
2 2 lori taylor
3 1 randy dave


Email

emailid (PK)
customerid
emailaddress

[Jun]

Hi Cynthia,

Here is the query you can try. Suppose you have two tabels:
Customer_info and Customer_email with the data structure you mentioned.

************************************************** *
SELECT a.customerID, lname, fname, emailaddress
FROM customer_info a Join Customer_email b
ON a.customerID = b.customerID
WHERE lname in
(
SELECT lname
FROM customer_info c Join Customer_email d
ON c.customerID = d.customerID
GROUP BY lname, fname, emailaddress
HAVING count(*) >1
AND fname = a.fname
AND emailaddress = b.emailaddress
)
ORDER BY lname, fname, emailaddress
********************************************


------------
Cynthia at 7/9/01 3:48:30 PM

Can anyone help me to write a query to show customers who have duplicate accounts with Email address, first name, and last name. this is the table structure is Customer table

customerid(PK)
accountno
fname
lname


Records will be

like this

customerid accountno fname lastname
1 2 lori taylor
2 2 lori taylor
3 1 randy dave


Email

emailid (PK)
customerid
emailaddress