Difficulty: Advanced
Purpose: Lists all phone numbers for both head of households for a household.
Page: Households

Field List

Households.Household_Name
,(SELECT Top 1 First_Name FROM Contacts C 
    WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1
      ORDER BY Gender_ID) AS Head_1_Name
,(SELECT Top 1 First_Name FROM Contacts C 
    WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 
    AND EXISTS (SELECT 1 FROM Contacts C2 WHERE C2.Household_Position_ID = 1 AND C2.Household_ID = C.Household_ID 
    GROUP BY Household_ID HAVING Count(*) > 1) 
      GROUP BY Contact_ID, First_Name, Gender_ID ORDER BY Gender_ID Desc) AS Head_2_Name
,Congregation_ID_Table.[Congregation_Name] AS [Congregation]
,Households.Home_Phone AS [Home Phone]
,(SELECT Top 1 Mobile_Phone FROM Contacts C 
    WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 
      ORDER BY Gender_ID) AS Head_1_Mobile
,(SELECT Top 1 Mobile_Phone FROM Contacts C 
    WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1
    AND EXISTS (SELECT 1 FROM Contacts C2 WHERE C2.Household_Position_ID = 1 AND C2.Household_ID = C.Household_ID 
    GROUP BY Household_ID HAVING Count(*) > 1) 
      GROUP BY Contact_ID, Mobile_Phone, Gender_ID ORDER BY Gender_ID Desc) AS Head_2_Mobile
,(SELECT Top 1 Company_Phone FROM Contacts C 
    WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 
      ORDER BY Gender_ID) AS Head_1_Company_Phone
,(SELECT Top 1 Company_Phone FROM Contacts C 
    WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 
    AND EXISTS (SELECT 1 FROM Contacts C2 WHERE C2.Household_Position_ID = 1 AND C2.Household_ID = C.Household_ID 
    GROUP BY Household_ID HAVING Count(*) > 1) 
      GROUP BY Contact_ID, Company_Phone, Gender_ID ORDER BY Gender_ID Desc) AS Head_2_Company_Phone
,Address_ID_Table.[Address_Line_1] AS [Street Address]
,Address_ID_Table.[City] AS [City]
,Address_ID_Table.[State/Region] AS [State]
,Address_ID_Table.[Postal_Code] AS [Zip Code]

Filter Clause

There is no set filter clause for this view, so simply select any desired criteria to refine your view. An example could be a specific congregation, as is shown below.

Congregation_ID_Table.Congregation_Name = 'Central Campus'

Techniques

• TOP SQL Function
• EXISTS SQL Function