This view shows active Heads of Households with minor children.

Difficulty: Intermediate
Purpose: Create list of head of households with minor children in the household.
Page: Contacts

Field List

Household_ID_Table.[Household_Name] AS [Household Name]
, Contacts.[Display_Name] AS [Display Name]
, Contacts.[First_Name] AS [First Name]
, Contact_Status_ID_Table.[Contact_Status] AS [Contact Status] 
, Household_ID_Table_Congregation_ID_Table.[Congregation_Name] AS [Congregation Name] 
, Household_Position_ID_Table.[Household_Position] AS [Household Position] 
, Participant_Record_Table_Participant_Type_ID_Table.[Participant_Type] AS [Participant Type]

View Clause

Contact_Status_ID_Table.[Contact_Status] = 'Active' 
AND Contacts.Household_Position_ID = 1
AND EXISTS (SELECT 1 FROM contacts c2 WHERE c2.household_id = Contacts.Household_ID 
      AND c2.Household_Position_ID = 2 AND c2.__Age < 18)

Techniques

• EXISTS