-- generate list of living people with additional info
SELECT a.Surname, a.Given, a.OwnerID, IIF(Sex,'F','M') AS Sex, IIF(Relate1>0,'Y','N') AS Related
,(select (SUBSTR(b.Date,4,4)||'-'||SUBSTR(b.Date,8,2)||'-'||SUBSTR(b.Date,10,2)) FROM eventtable b WHERE b.OwnerID = a.OwnerID AND b.eventType = 1) as Birth
, (select (select name from PlaceTable c where c.PlaceID=b.PlaceID AND b.OwnerID = a.OwnerID AND b.eventType = 1) FROM eventtable b WHERE b.OwnerID = a.OwnerID AND b.eventType = 1) AS BirthPlace
, (SELECT (SELECT Surname FROM NameTable c WHERE c.OwnerID=IIF(Sex,d.FatherID,d.MotherID) ) FROM FamilyTable d WHERE d.FamilyID=SpouseID) AS SpouseSurname
, (SELECT (SELECT Given FROM NameTable c WHERE c.OwnerID=IIF(Sex,d.FatherID,d.MotherID) )FROM FamilyTable d WHERE d.FamilyID=SpouseID) AS SpouseGiven
FROM NameTable a
INNER JOIN PersonTable ON PersonTable.PersonID = a.OwnerID
WHERE
Living = 1
AND IsPrimary = 1
AND a.Surname NOT LIKE '' and a.Surname NOT LIKE 'Unknown'
ORDER BY Surname, Given
;