Phone Numbers for a Household
Difficulty: Advanced
Purpose: Lists all phone numbers for both Heads of Household 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 the criteria you want 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