If you could give us some sample data in table form it would be a lot easier to help you.

I could be totally off-base, but my first thought is to use multiple CTEs. I'm not sure that recursion is even necessary. Without the tables or data this is just pseduo-code so run with it.

WITH

cteHouses AS --step 1 - Get all House Ids

(SELECT * FROM HouseTable),

cteResidents AS --step 2 - For each house id, get all people linked directly to that house

(SELECT *

FROM cteHouses h

INNER JOIN ResidentsTable r

ON h.houseID = r.HouseID),

cteHouseXref AS --step 3 - for each of those people , get any additional house ids linked to them

(SELECT *

FROM cteHouses h1

INNER JOIN ResidentsTable r1

ON h1.houseID = r1.HouseID),

cteResidentXref AS --step 4 - for each of those additional house ids, get any additional people

(SELECT *

FROM cteHouseXref h2

INNER JOIN ResidentsTable r2

ON h2.houseID = r2.HouseID)

SELECT

*

FROM

cteHouses h3

INNER JOIN

cteResidents r3

ON h3.houseID = r3.HouseID

INNER JOIN

cteHouseXref r4

ON h4.houseID = r4.HouseID

INNER JOIN

cteResidentXref r5

ON h5.houseID = r5.HouseID