Printable View
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
emailid (PK)
customerid
emailaddress
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
emailid (PK)
customerid
emailaddress
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
emailid (PK)
customerid
emailaddress