The following View shows Contact that have the same email but does not have a Contact Relationship with one another.

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 (e.g. 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