Try

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

>
>I have to admit I am weak on this topic and would like to get better. I need a stored procedure where I can retrieve a record from a single table but the parameter passed in could be a match to a column in this table or it could be found in another. Here is the table structure.
>
>

>*** Household ***
>HouseholdID (UniqueID)
>LocationID (UniqueID)
>ApplicantID (UniqueID)  -> 1-1 to Persons
>
>*** HouseholdMembers ***
>HouseHoldID  (UniqueID)         -> HouseHold
>PersonId  (UniqueID)              -> Person
>RelationshipRoleID (UniqueID)  ->RelationshipRole
>DateAddedToHousehold (DateTime)
>

>
>What I need is to obtain the single row from Household and pass in a PersonID. The PersonID may be found in household in the ApplicantID column but if not, then it would be found in the HouseholdMembers table and would give the HouseholdID value there to get the row from.
>
>Any help appreciated to get me pointed to creating this stored procedure. Thanks
>Tim