-- ParentSingleMerge.sql
/* 2017-02-11 Tom Holden ve3meo
rev 2017-02-14 corrected error 
Combines children in multiple 'families' of a single common parent 
into one 'family', the one with the lowest MRIN. In RM, a single parent 
has a spouse whose RIN=0 (i.e., is RINless and there is nothing further
in the database). There are instances where a single parent can be part 
of multiple 'families' or couples, each with a spouse of RIN=0 and each 
with children. 
*/

-- make a temp table of children with a RINless parent ()
DROP TABLE IF EXISTS xChildParentUnknown
;

CREATE TEMP TABLE xChildParentUnknown AS
SELECT Ch.ChildID, Ch.FamilyID, Fm.FatherID, Fm.MotherID, NULL AS NewFamilyID
FROM ChildTable Ch
JOIN FamilyTable Fm
USING(FamilyID)
WHERE Fm.FatherID = 0
OR    Fm.MotherID = 0
ORDER BY Ch.FamilyID DESC
;

-- set a view to get the lowest MRIN of each identical parent couple, 1 of whom is RINless
DROP VIEW IF EXISTS vFamGroups
;
CREATE TEMP VIEW vFamGroups AS
SELECT *, COUNT() FROM xChildParentUnknown
GROUP BY FatherID, MotherID
;


-- assign to the temp table the lowest MRIN for each identical parent couple
UPDATE xChildParentUnknown 
SET NewFamilyID = 
 (SELECT FamilyID 
    FROM vFamGroups FG 
    WHERE xChildParentUnknown.FatherID = FG.FatherID -- need explicit table.column name to resolve unreported ambiguity
    AND xChildParentUnknown.MotherID = FG.MotherID
 )
;

-- replace the FamilyID for children in the ChildTable for matching children in the temp table with the lowest MRIN
UPDATE ChildTable
SET FamilyID = 
 (SELECT NewFamilyID 
  FROM xChildParentUnknown X 
  WHERE ChildTable.ChildID = X.ChildID 
  AND ChildTable.FamilyID = X.FamilyID
  )
WHERE ChildID 
  IN 
  (SELECT ChildID 
   FROM xChildParentUnknown ASC
   )
;

-- delete the couples without the lowest MRIN 
DELETE FROM FamilyTable
WHERE FamilyID IN 
 (
  SELECT DISTINCT FamilyID 
  FROM xChildParentUnknown X 
  WHERE X.FamilyID != X.NewFamilyID 
  ORDER BY FamilyID
  )
;
--end of script