Identifying Duplicate entries with different ID'S 1

[ashab02]

Hello
I have a set of data and what I want to do is identify within this data repeat cases with the same address and name details but different ID'S
I have so far managed to identify the cases where the same address and name details appear but not able to identify the ID. I want to now attach the ID to these records, can someone give me some guidance on this.
So far I have used (simplified):

SELECT COUNT(*) FORENAME,SURNAME,ADD1, ADD2
FROM CUSTOMER
GROUP BY FORENAME,SURNAME,ADD1, ADD2
HAVING COUNT(*) > 1

The above returns the duplicate cases. I now want to know the ID's for all these cases.

 

[bborissov]

SELECT Id, Customer.ForeName,
       Customer.SurName,
       Customer.Add1,
       Customer.Add2
FROM CUSTOMER
INNER JOIN (SELECT FORENAME,SURNAME,ADD1, ADD2
                   FROM CUSTOMER
                   GROUP BY FORENAME,SURNAME,ADD1, ADD2
                   HAVING COUNT(*) > 1) Tbl1
ON CUSTOMER.ForeName = Tbl1.ForeName AND
   CUSTOMER.SurName  = Tbl1.SurName  AND
   CUSTOMER.Add1     = Tbl1.Add1     AND
   CUSTOMER.Add2     = Tbl1.Add2

Not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.