-- DuplicateCouples-CreateChildView.sql
/* 2026-08-15 by Gemini Flash directed by Tom Holden ve3meo
Stored query (temporary) lists children of duplicate couples.
*/ 
DROP VIEW IF EXISTS DuplicateCouplesChildren;
CREATE TEMP VIEW DuplicateCouplesChildren AS
WITH DuplicateCouples AS (
    SELECT 
        FatherID, 
        MotherID,
        GROUP_CONCAT(FamilyID, ', ') AS AllFamilyIDs
    FROM FamilyTable
    WHERE FatherID > 0 OR MotherID > 0
    GROUP BY FatherID, MotherID
    HAVING COUNT(FamilyID) > 1
)
SELECT DISTINCT
    c.ChildID,
    COALESCE(n.Surname || ', ' || n.Given, '[Unknown Child]') AS ChildName,
    f.FatherID,
    f.MotherID,
    dc.AllFamilyIDs AS CoupleFamilyIDs
FROM ChildTable c
JOIN FamilyTable f 
    ON c.FamilyID = f.FamilyID
JOIN DuplicateCouples dc 
    ON f.FatherID = dc.FatherID 
   AND f.MotherID = dc.MotherID
LEFT JOIN NameTable n 
    ON n.OwnerID = c.ChildID 
   AND n.IsPrimary = 1
ORDER BY ChildName;