Thanks Naomi,

I am understanding now.

>>Thanks Sergey,
>>
>>The puzzling part for me was how the primary Select statement gets a match to the subquery. While this probably isn't valid SQL, I would have expected something like this where we specify we are looking for HouseholdID in the subquery to match the HouseholdID in the main query.
>>

>>SELECT *
>>  FROM Household 
>>  WHERE ApplicantID = @PersonId  
>>    OR HouseholdID = (SELECT HouseholdID FROM HouseholdMembers WHERE PersonId =  @PersonId)
>>

>
>
>>In that case where trying to specifically select HouseholdID from the subquery.
>>
>>I guess it is this part of the subquery that does that magic. WHERE HouseholdID = Household.HouseholdID AND PersonId = @PersonId
>>
>>Basically it works so I am happy, but just trying to figure out so I understand.
>>Thanks a bunch, I think I am making sense of it now.
>>Tim
>>
>
>What you wrote is essentially the same as EXISTS query. But EXISTS is just a better syntax and also it does not suffer from the NULL problem that IN query suffers.
>
>In order to CONNECT outer and inner queries, you use this
>

>select * from HouseHold H1
>where exists (select 1 (you can put * with the same effect here)
>from HouseholdMembers M where H1.HouseHoldID = M.HouseHoldID -- this is the connection between outer and inner
>AND M.PersonID = @PersonID)