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

>Hi Tim,
>
>An EXISTS type subquery does not return data. SELECT 1 is used to prevent SQL Server from doing extra processing on the column list which will not be used anyway.
>An EXISTS type subquery only tells if at least one record satisfying conditions can be found.
>
>
>>That was fast and it does the trick. Just tested and works perfect. Thank you very much. Just for my understanding, is the Select 1 mean only one record? In the data that shouldn't happen but is that to prevent in case it was found? Also, how does the sub query return a householdID to match to the household?
>>Thanks
>>Tim
>>
>>>Try
>>>
>>>

>>>SELECT *
>>>  FROM Household 
>>>  WHERE ApplicantID = @PersonId  
>>>    OR EXISTS (SELECT 1 FROM HouseholdMembers WHERE HouseholdID = Household.HouseholdID AND PersonId =  @PersonId)
>>>

>>>