-- PersonsBegatChildren.sql 
-- Returns birth of child as 'Fact' for parent in same columnar layout as AllFacts4Person.sql and could be integrated therewith.
-- If Count>1, then it is most likely that there are multiple birth facts for the child.
-- 'Fathered' and 'Mothered' might be replaced by 'Begat' or 'Sired' and 'Bore'
-- 2010-01-13 ve3meo
-- rev 2021-03-03 ve3meo
--- Labelled Parent name columns and tested on RM7 and RM8

-- Fathers 
SELECT  'Fathered' AS Fact, 'Principal' AS 'Role Type', p.personid AS RIN
  , n.surname COLLATE NOCASE AS 'Surname', n.suffix COLLATE NOCASE AS 'Suffix', n.prefix COLLATE NOCASE AS 'Prefix', n.given COLLATE NOCASE AS Given
  , c.childid
  , n2.surname COLLATE NOCASE AS 'Child Surname', n2.suffix COLLATE NOCASE AS 'Child Suffix', n2.prefix COLLATE NOCASE AS 'Child Prefix', n2.given COLLATE NOCASE AS 'Child Given Name', COUNT(1) AS Count 
FROM  persontable p, nametable n, familytable f, childtable c, nametable n2, 
  eventtable e, facttypetable f2
WHERE  p.personid=n.ownerid AND p.personid=f.fatherid AND f.familyid=c.familyid AND 
  c.childid=n2.ownerid AND c.childid=e.ownerid AND e.eventtype=f2.facttypeid   
  AND f2.FactTypeID=1 AND n.IsPrimary = 1 AND c.RelFather=0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

UNION ALL

-- Mothers
SELECT  'Mothered' AS Fact, 'Principal' AS 'Role Type', p.personid AS RIN, n.surname COLLATE NOCASE, n.suffix COLLATE NOCASE, n.prefix COLLATE NOCASE, n.given COLLATE NOCASE, c.childid, 
  n2.surname COLLATE NOCASE AS 'Child Surname', n2.suffix COLLATE NOCASE AS 'Child Suffix', n2.prefix COLLATE NOCASE AS 'Child Prefix', n2.given COLLATE NOCASE AS 'Child Given Name', COUNT(1) AS Count 
FROM  persontable p, nametable n, familytable f, childtable c, nametable n2, 
  eventtable e, facttypetable f2
WHERE  p.personid=n.ownerid AND p.personid=f.motherid AND f.familyid=c.familyid AND 
  c.childid=n2.ownerid AND c.childid=e.ownerid AND e.eventtype=f2.facttypeid   
  AND f2.FactTypeID=1 AND n.IsPrimary = 1 AND c.RelMother=0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12