Query to find users with multiple E-mail addresses of the same name (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
select aaau.user_id,aaau.first_name,aaaci.emailid,sdu.status,sdo.name
"Site",adef.org_name "Account" from aaauser aaau LEFT JOIN
aaausercontactinfo aaauci ON aaauci.user_id=aaau.user_id LEFT JOIN
aaacontactinfo aaaci ON aaaci.contactinfo_id=aaauci.contactinfo_id LEFT
JOIN sduser sdu ON sdu.userid=aaau.user_id LEFT JOIN UserDepartment
userDept ON sdu.USERID=userDept.USERID LEFT JOIN DepartmentDefinition
deptTable ON userDept.DEPTID=deptTable.DEPTID LEFT JOIN sitedefinition
sdef ON sdef.siteid=deptTable.siteid LEFT JOIN accountsitemapping asm ON
asm.siteid=sdef.siteid LEFT JOIN accountdefinition adef ON
adef.org_id=asm.accountid LEFT JOIN sdorganization sdo ON
sdo.org_id=sdef.siteid where aaaci.emailid in(select emailid from
aaacontactinfo where emailid !='' group by emailid HAVING count(*) >
1) group by
aaau.first_name,aaau.user_id,aaaci.emailid,sdu.status,sdo.name,adef.org_name
order by aaaci.emailid,aaau.first_name;