-- Parents-Three_Childrens_Names.sql
-- was three_childrens_names.sql
-- Jerry Bryan, 22 May 2020
-- This query searches for parents based on given names of their children.
-- It gets a hit when three or more children have any primary or alternate
-- given names that are in a hardwired list. The report lists the parents.

SELECT COUNT(C.ChildID) AS fam_size,
F.FatherID, father.Given, father.Surname, F.MotherID, mother.Given, mother.Surname
FROM ChildTable AS C
JOIN
(SELECT DISTINCT NN.OwnerID
FROM NameTable AS NN
WHERE NN.Given LIKE("%Mary%") OR NN.Given LIKE ("%John%") OR NN.Given LIKE("%Elizabeth%"))
AS N ON N.OwnerID = C.ChildID
JOIN
FamilyTable AS F ON F.FamilyID = C.FamilyID
JOIN
NameTable AS father ON father.OwnerID = F.FatherID AND father.IsPrimary
JOIN
NameTable AS mother ON mother.OwnerID = F.MotherID AND mother.IsPrimary
GROUP BY C.FamilyID
HAVING fam_size >= 3
ORDER BY father.Surname, father.Given, mother.Surname, mother.Given;