Stored Procedure : How To Output Parent Child Etc Data

Q: How to Output Parent Child Data using 2, 3 or more related tables.


say you have 3 related tables where the Bold HouseID is the one to many relationship

Houses
HouseID
Name
add1


HouseOwer
AutoID
HouseID
Name
add1

HouseCorrespondent
AutoID
HouseID
Name
add1


Loop For each HouseID

step through the database
first get the ParentID from the Houses table
return that data

then get the related from the house owners table
return that data

then get the related correspondents from the House Correspondent table
return that data

end loop

ie return the date like below


Code:

HouseID | Name | add1
1 House1 abc Address
1 Mr abc Mr abc Ower Address
1 Mr abc Mr abc Correspondent Address

2 House2 def Address
2 Mr def Mr def Ower Address
2 Mr def Mr def Correspondent Address

3 House3 ghi Address
3 Mr ghi Mr ghi Ower Address



I hope ive explained well enough