Fix: Same Email No Relationship
The following View shows Contact that have the same email but does not have a Contact Relationship with one another.
Difficulty: Advanced
Purpose: For use by the Data Quality Team to assist in identifying possible duplicate records not found by the Duplicate Finder Routine.
Page: Contacts
Field List
Leave this blank to use the default fields. Alternatively, you can add any of the following fields to show helpful details for identifying duplicates:
Contacts.Last_Name ,Contacts.Nickname ,Contacts.First_Name ,Contacts.Display_Name ,Contacts.Email_Address ,Contact_Status_ID_Table.Contact_Status ,Household_ID_Table.Home_Phone ,Contacts.Mobile_Phone ,Household_ID_Table_Address_ID_Table.Address_Line_1 ,Household_ID_Table_Address_ID_Table.City ,Household_ID_Table_Address_ID_Table.[State/Region] AS State ,Household_ID_Table_Address_ID_Table.Postal_Code ,Convert(Varchar(12),Contacts.Date_of_Birth,101) AS Date_of_Birth ,Gender_ID_Table.Gender ,Marital_Status_ID_Table.Marital_Status ,Household_ID_Table_Congregation_ID_Table.Congregation_Name ,Household_ID_Table.Household_Name ,Household_Position_ID_Table.Household_Position ,[dp_Created].[User_Name] AS [Created By] ,[dp_Updated].[User_Name] AS [Updated By]View Clause
The following clause limits the list of Contacts to those with the same email address and no Contact Relationship. Contacts with the same email, but with a Contact Relationship (for example, Married To: or Child Of):
Contacts.Email_Address IS NOT NULL AND EXISTS (SELECT 1 FROM Contacts C WHERE C.Email_Address IS NOT NULL AND C.Company = Contacts.Company AND C.Email_Address = Contacts.Email_Address AND C.Contact_ID <> Contacts.Contact_ID AND ISNULL(C.Household_ID,0) <> ISNULL(Contacts.Household_ID,0) AND NOT EXISTS (SELECT 1 FROM Contact_Relationships CR WHERE CR.Contact_ID = Contacts.Contact_ID AND CR.Related_Contact_ID = C.Contact_ID))Techniques
- EXISTS SQL Function