-- Stats-MaxChildren.sql
/*
2016-02-12 Tom Holden ve3meo
Returns the father and mother from the database having the most children,
from any number of spouses.
Creates 3 temporary views.
*/


DROP VIEW IF EXISTS vChildrenByFamily
;
CREATE TEMP VIEW vChildrenByFamily AS
SELECT FamilyID, COUNT() AS Children FROM ChildTable GROUP BY FamilyID
;

DROP VIEW IF EXISTS vChildrenByFather
;
CREATE TEMP VIEW vChildrenByFather AS
SELECT FatherID, SUM(Children) AS Children FROM vChildrenByFamily JOIN FamilyTable USING(FamilyID)
GROUP BY FatherID
;

DROP VIEW IF EXISTS vChildrenByMother
;
CREATE TEMP VIEW vChildrenByMother AS
SELECT MotherID, SUM(Children) AS Children FROM vChildrenByFamily JOIN FamilyTable USING(FamilyID)
GROUP BY MotherID
;

SELECT 'Father' AS Parent, FatherID AS RIN, MAX(Children) AS Children FROM vChildrenByFather
WHERE FatherID > 0
UNION
SELECT 'Mother' AS Parent, MotherID AS RIN, MAX(Children) AS Children FROM vChildrenByMother
WHERE MotherID > 0
;

-- end of script