I want to produce a query which return a single row for each house which includes columns for each relationship between PERSNO=1 and the other residents, something like this.
aacode Relationship2, Relationship3, Realtionship4, etc
This is the code I produced so far:
Expand|Select|Wrap|Line Numbers
- SELECT People.ID, People.aacode, People.PERSNO, People.HRP, People.DVHsize,
- (SELECT Fam2.R01 FROM People AS Fam2 WHERE Fam2.aacode = People.aacode AND Fam2.PERSNO = 2) AS Rel2,
- (SELECT Fam3.R01 FROM People AS Fam3 WHERE Fam3.aacode = People.aacode AND Fam3.PERSNO = 3) AS Rel3,
- (SELECT Fam4.R01 FROM People AS Fam4 WHERE Fam3.aacode = People.aacode AND Fam4.PERSNO = 4) AS Rel4
- WHERE (((People.HRP)=[People.PERSNO]))
- ORDER BY People.aacode;
I attempted to modify my subquery to
Expand|Select|Wrap|Line Numbers
- (SELECT Relatives.Relationship FROM Relatives
- LEFT JOIN (SELECT People.R01 FROM People) AS Fam2 ON Relatives.ID = Fam2.R01
- WHERE (Fam2.aacode = People.aacode AND Fam2.PERSNO = 2)) AS Rel2,