>>I'd say, if the design requires this complicated algorithm, it may be not a good design.
>>
>>Anyway, here is a possible solution
>>
>>
>>select HouseHoldID, ApplicantID from HouseHold where ApplicantID = @PersonID
>>UNION
>>select HouseHoldID, PersonID from HouseHoldMembers where PersonID = @PersonID
>
>
>Hi Naomi,
>Thanks. I am not sure why it is complicated though. The solution that Sergey gave me worked and didn't seem complicated. Am I missing something?
>Tim
May be it's OK for this particular case, but I prefer to store information related to an entity in one table and therefore always retrieve it from one table.
BTW, I think for your case you still don't need to go to the first table and will always retrieve it from the second if it describes all people (main applicants or not). In other words, on the second thought just do a search in the HouseHoldMembers for the PersonID.
On the other hand, the Household table may be smaller than HouseHoldMembers table, so it may make sense to check it first.
If it's not broken, fix it until it is.
My Blog