/* TwinsList.sql
2024-08-21 Tom Holden ve3meo
Lists persons with one or more siblings born on the same date
Should work on all RM versions from 4 to 10.
Developed and tested on SQLiteSpy 1.9.16 Win64 with fake RMNOCASE extension
*/
SELECT DISTINCT
       ChildID1 RIN1, Given1, Surname1 Surname
     , Given2, ChildID2 RIN2, Date1 Born  
-- DISTINCT precludes repetition when primary child has multiple Birth events on same date
FROM
(
SELECT C1.ChildID ChildID1, N1.Given Given1, N1.Surname Surname1
     , C2.ChildID ChildID2, N2.Given Given2, N2.Surname Surname2
     , E1.Date Date1, E2.Date Date2 
FROM ChildTable C1
JOIN ChildTable C2 USING(FamilyID)
JOIN EventTable E1 ON ChildID1=E1.OwnerID AND E1.OwnerType=0 AND E1.EventType=1 --Birth events
JOIN EventTable E2 ON ChildID2=E2.OwnerID AND E2.OwnerType=0 AND E2.EventType=1 --Birth events
JOIN NameTable N1 ON ChildID1=N1.OwnerID AND N1.IsPrimary
JOIN NameTable N2 ON ChildID2=N2.OwnerID AND N2.IsPrimary
WHERE C1.ChildID < C2.ChildID -- preclude reverse pairs and self-pairs
  AND E1.Date=E2.Date      -- matching birth dates
  AND E1.Date NOT LIKE '.' -- preclude empty birth date
ORDER BY ChildID1
)
;