tbl_family_relationships.sql

-- A and B are children of C
INSERT INTO family_relationships (child_id, parent_id, relation_type) VALUES
(1, 3, 'father'),
(2, 3, 'father');

-- C is a child of D
INSERT INTO family_relationships (child_id, parent_id, relation_type) VALUES
(3, 4, 'father');

INSERT INTO `family_relationships` (`id`, `child_id`, `parent_id`, `relation_type`, `created_at`) VALUES
(NULL, '1', '5', 'mother', current_timestamp()),
(NULL, '2', '5', 'mother', current_timestamp());

-- Select Query

WITH RECURSIVE descendants AS (
  SELECT
    fr.parent_id,
    fr.child_id,
    1 AS level
  FROM family_relationships fr
  WHERE fr.parent_id = 4

  UNION ALL

  SELECT
    fr.parent_id,
    fr.child_id,
    d.level + 1
  FROM family_relationships fr
  JOIN descendants d ON fr.parent_id = d.child_id
)
SELECT
  d.level,
  r.id,
  CONCAT(r.first_name, ' ', r.middle_name, ' ', r.last_name) AS full_name
FROM descendants d
JOIN tbl_registration r ON r.id = d.child_id
ORDER BY d.level;

-- Select Query Father With Mother

SELECT
    c.id AS child_id,
    CONCAT(c.first_name, ' ', c.middle_name, ' ', c.last_name) AS child_name,

    -- Father
    CONCAT(f.first_name, ' ', f.middle_name, ' ', f.last_name) AS father_name,

    -- Mother
    CONCAT(m.first_name, ' ', m.middle_name, ' ', m.last_name) AS mother_name

FROM tbl_registration c

-- Father Join
LEFT JOIN family_relationships fr_f ON fr_f.child_id = c.id AND fr_f.relation_type = 'father'
LEFT JOIN tbl_registration f ON fr_f.parent_id = f.id

-- Mother Join
LEFT JOIN family_relationships fr_m ON fr_m.child_id = c.id AND fr_m.relation_type = 'mother'
LEFT JOIN tbl_registration m ON fr_m.parent_id = m.id

WHERE c.id IN (1,2)

ORDER BY c.id;