Query to find duplicate requester records
Hi,
Because of a change in e-mailaddresses some requesters are registered twice after AD-sync. I am trying to create a list of requesters with two records in the database. I have made a query out of gathered information from the forum to get a list of double records of users, so I can remove the . The query shows all users that occur > 1 times in the database. Only, I'm missing double records in the results.
SELECT AaaUser.FIRST_NAME, AaaContactInfo.EMAILID FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
Where SDUser.STATUS='ACTIVE'
AND AaaUser.USER_ID not in (select TechnicianID from HelpDeskCrew)
GROUP BY AaaUser.FIRST_NAME, AaaContactInfo.EMAILID
HAVING COUNT(AaaUser.FIRST_NAME) > 1
When I execute a simple query with only "FIRST_NAME" listed I'm getting more results.
SELECT AaaUser.FIRST_NAME FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
Where SDUser.STATUS='ACTIVE'
AND AaaUser.USER_ID not in (select TechnicianID from HelpDeskCrew)
GROUP BY AaaUser.FIRST_NAME
HAVING COUNT(AaaUser.FIRST_NAME) > 1
Can you please assist me with the correct query that shows Full name, e-mail address and Login name where Full name occurs more than once?
Regards,
Olivier