Duplikate finden

Um in einer Benutzerdatenbank Duplikate ausfindig zu machen, in der jedoch ausschließlich LDAP-IDs statt Namen gespeichert werden, kann man folgenden Query verwenden:

USE myApplicationsDB;
SELECT u.idUser, uc.dupeCount
FROM tblUsers u
INNER JOIN (
    SELECT LDAPGUID, COUNT(*) AS dupeCount
    FROM tblUsers
    GROUP BY LDAPGUID
    HAVING COUNT(*) > 1
) uc ON u.LDAPGUID = uc.LDAPGUID

Ein eleganterer Weg, um z.B. in einer Termindatenbank Duplikate hintereinander aufzulisten, wäre folgender:

SELECT ID, Employees_ID, Customer_ID, Begin, Description
FROM Appointments
WHERE EXISTS (
    SELECT ID FROM Appointments Dup WHERE Appointments.Employees_ID = Dup.Employees_ID AND
                                          Appointments.Customer_ID = Dup.Customer_ID AND
                                          Appointments.Begin = Dup.Begin AND
                                          Appointments.Description = Dup.Description AND
                                          Appointments.ID <> Dup.ID)
ORDER BY ID;