tbl_family_relationships.sql

-- Get Surname With Translation

SELECT
    s.id AS surname_id,
    s.surname,
    MAX(CASE WHEN l.code = 'gu' THEN st.translation END) AS st_gu,
    MAX(CASE WHEN l.code = 'hi' THEN st.translation END) AS st_hi
FROM
    surname s
LEFT JOIN
    surname_translation st ON s.id = st.surname_id
LEFT JOIN
    languages l ON st.language_code_id = l.id
GROUP BY
    s.id, s.surname;

-- 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;

-- 2 Select Query Father With Mother

SELECT
    c.id AS person_id,
    CONCAT(c.first_name, ' ', c.middle_name, ' ', c.last_name) AS person_name,

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

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

    -- Spouse (wife/husband)
    COALESCE(
        CONCAT(s.first_name, ' ', s.middle_name, ' ', s.last_name),
        'Not Found'
    ) AS spouse_name,

    -- Maiden name (if available)
    COALESCE(
        (SELECT CONCAT(mn.maiden_first_name, ' ', mn.maiden_middle_name, ' ', mn.maiden_last_name)
         FROM tbl_maiden_name mn
         WHERE mn.registration_id = c.id),
        'Not Found'
    ) AS maiden_name

FROM tbl_registration c

-- Father Join (relation_type_id = 1 for father)
LEFT JOIN tbl_family_relationships fr_f ON fr_f.person_id = c.id
    AND fr_f.relation_type_id = (SELECT id FROM tbl_relation_types WHERE name = 'father')
LEFT JOIN tbl_registration f ON fr_f.related_person_id = f.id

-- Mother Join (relation_type_id = 2 for mother)
LEFT JOIN tbl_family_relationships fr_m ON fr_m.person_id = c.id
    AND fr_m.relation_type_id = (SELECT id FROM tbl_relation_types WHERE name = 'mother')
LEFT JOIN tbl_registration m ON fr_m.related_person_id = m.id

-- Spouse Join (relation_type_id = 4 for wife or 5 for husband)
LEFT JOIN tbl_family_relationships fr_s ON fr_s.person_id = c.id
    AND fr_s.relation_type_id IN (
        SELECT id FROM tbl_relation_types
        WHERE name IN ('wife', 'husband')
    )
LEFT JOIN tbl_registration s ON fr_s.related_person_id = s.id

WHERE c.id IN (1, 7)
ORDER BY c.id;

------------------------------------

WITH RECURSIVE family_tree AS (
    -- Start with the base generation (you can change the starting ID)
    SELECT
        id,
        0 AS generation,
        CAST(id AS CHAR(1000)) AS path
    FROM tbl_registration
    WHERE id IN (1, 2)

    UNION ALL

    -- Find all ancestors (parents)
    SELECT
        r.id,
        ft.generation - 1,
        CONCAT(ft.path, ',', r.id)
    FROM family_tree ft
    JOIN tbl_family_relationships fr ON fr.person_id = ft.id
    JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id
        AND rt.name IN ('father', 'mother')
    JOIN tbl_registration r ON fr.related_person_id = r.id
    WHERE FIND_IN_SET(r.id, ft.path) = 0  -- Prevent cycles

    UNION ALL

    -- Find all descendants (children)
    SELECT
        r.id,
        ft.generation + 1,
        CONCAT(ft.path, ',', r.id)
    FROM family_tree ft
    JOIN tbl_family_relationships fr ON fr.related_person_id = ft.id
    JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id
        AND rt.name = 'child'
    JOIN tbl_registration r ON fr.person_id = r.id
    WHERE FIND_IN_SET(r.id, ft.path) = 0  -- Prevent cycles
)

SELECT
    ft.generation,
    r.id,
    CONCAT(r.first_name, ' ', r.middle_name, ' ', r.last_name) AS person_name,

    -- Father
    COALESCE((
        SELECT CONCAT(f.first_name, ' ', f.middle_name, ' ', f.last_name)
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name = 'father'
        JOIN tbl_registration f ON fr.related_person_id = f.id
        WHERE fr.person_id = r.id
    ), 'Not Found') AS father,

    -- Mother
    COALESCE((
        SELECT CONCAT(m.first_name, ' ', m.middle_name, ' ', m.last_name)
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name = 'mother'
        JOIN tbl_registration m ON fr.related_person_id = m.id
        WHERE fr.person_id = r.id
    ), 'Not Found') AS mother,

    -- Spouse
    COALESCE((
        SELECT CONCAT(s.first_name, ' ', s.middle_name, ' ', s.last_name)
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name IN ('wife', 'husband')
        JOIN tbl_registration s ON fr.related_person_id = s.id
        WHERE fr.person_id = r.id
    ), 'Not Found') AS spouse,

    -- Maiden name
    COALESCE((
        SELECT CONCAT(mn.maiden_first_name, ' ', mn.maiden_middle_name, ' ', mn.maiden_last_name)
        FROM tbl_maiden_name mn
        WHERE mn.registration_id = r.id
    ), 'Not Found') AS maiden_name,

    -- Children list
    (
        SELECT GROUP_CONCAT(CONCAT(ch.first_name, ' ', ch.middle_name, ' ', ch.last_name) SEPARATOR ', ')
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name = 'child'
        JOIN tbl_registration ch ON fr.person_id = ch.id
        WHERE fr.related_person_id = r.id
    ) AS children

FROM family_tree ft
JOIN tbl_registration r ON ft.id = r.id
GROUP BY r.id  -- Ensure each person appears only once
ORDER BY ft.generation, r.id;

------------------------------------

WITH RECURSIVE family_tree AS (
    -- Start with base individuals (Virajsinh and Keyur)
    SELECT
        id,
        0 AS generation,
        CAST(id AS CHAR(1000)) AS path
    FROM tbl_registration
    WHERE id IN (1)  -- Virajsinh and Keyur

    UNION ALL

    -- Find ancestors (parents)
    SELECT
        r.id,
        ft.generation - 1,
        CONCAT(ft.path, ',', r.id)
    FROM family_tree ft
    JOIN tbl_family_relationships fr ON fr.person_id = ft.id
    JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id
        AND rt.name IN ('father', 'mother')
    JOIN tbl_registration r ON fr.related_person_id = r.id
    WHERE FIND_IN_SET(r.id, ft.path) = 0

    UNION ALL

    -- Find descendants (children)
    SELECT
        r.id,
        ft.generation + 1,
        CONCAT(ft.path, ',', r.id)
    FROM family_tree ft
    JOIN tbl_family_relationships fr ON fr.related_person_id = ft.id
    JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id
        AND rt.reverse_relation = 'parent'
    JOIN tbl_registration r ON fr.person_id = r.id
    WHERE FIND_IN_SET(r.id, ft.path) = 0

    UNION ALL

    -- Find spouses
    SELECT
        r.id,
        ft.generation,  -- Same generation as the person
        CONCAT(ft.path, ',', r.id)
    FROM family_tree ft
    JOIN tbl_family_relationships fr ON fr.person_id = ft.id
    JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id
        AND rt.name IN ('wife', 'husband')
    JOIN tbl_registration r ON fr.related_person_id = r.id
    WHERE FIND_IN_SET(r.id, ft.path) = 0
)

SELECT
    ft.generation,
    r.id,
    CONCAT(r.first_name, ' ', r.middle_name, ' ', r.last_name) AS person_name,

    -- Father
    COALESCE((
        SELECT CONCAT(f.first_name, ' ', f.middle_name, ' ', f.last_name)
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name = 'father'
        JOIN tbl_registration f ON fr.related_person_id = f.id
        WHERE fr.person_id = r.id
    ), 'Not Found') AS father,

    -- Mother
    COALESCE((
        SELECT CONCAT(m.first_name, ' ', m.middle_name, ' ', m.last_name)
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name = 'mother'
        JOIN tbl_registration m ON fr.related_person_id = m.id
        WHERE fr.person_id = r.id
    ), 'Not Found') AS mother,

    -- Spouse
    COALESCE((
        SELECT CONCAT(s.first_name, ' ', s.middle_name, ' ', s.last_name)
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name IN ('wife', 'husband')
        JOIN tbl_registration s ON fr.related_person_id = s.id
        WHERE fr.person_id = r.id
    ), 'Not Found') AS spouse,

    -- Maiden name
    COALESCE((
        SELECT CONCAT(mn.maiden_first_name, ' ', mn.maiden_middle_name, ' ', mn.maiden_last_name)
        FROM tbl_maiden_name mn
        WHERE mn.registration_id = r.id
    ), 'Not Found') AS maiden_name,

    -- Corrected children list
    (
        SELECT GROUP_CONCAT(
            CONCAT(ch.first_name, ' ', ch.middle_name, ' ', ch.last_name)
            ORDER BY ch.id SEPARATOR ', '
        )
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.reverse_relation = 'parent'
        JOIN tbl_registration ch ON fr.person_id = ch.id
        WHERE fr.related_person_id = r.id
    ) AS children

FROM family_tree ft
JOIN tbl_registration r ON ft.id = r.id
GROUP BY r.id
ORDER BY ft.generation, r.id;

------------------------------------

WITH RECURSIVE family_tree AS (
    -- Start with Virajsinh (ID 1)
    SELECT
        id,
        0 AS generation,
        CAST(id AS CHAR(1000)) AS path
    FROM tbl_registration
    WHERE id = 1  -- Starting with Virajsinh

    UNION ALL

    -- Find ancestors (parents)
    SELECT
        r.id,
        ft.generation - 1,
        CONCAT(ft.path, ',', r.id)
    FROM family_tree ft
    JOIN tbl_family_relationships fr ON fr.person_id = ft.id
    JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id
        AND rt.name IN ('father', 'mother')
    JOIN tbl_registration r ON fr.related_person_id = r.id
    WHERE FIND_IN_SET(r.id, ft.path) = 0

    UNION ALL

    -- Find descendants (children)
    SELECT
        r.id,
        ft.generation + 1,
        CONCAT(ft.path, ',', r.id)
    FROM family_tree ft
    JOIN tbl_family_relationships fr ON fr.related_person_id = ft.id
    JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id
        AND rt.reverse_relation = 'parent'
    JOIN tbl_registration r ON fr.person_id = r.id
    WHERE FIND_IN_SET(r.id, ft.path) = 0

    UNION ALL

    -- Find spouses
    SELECT
        r.id,
        ft.generation,
        CONCAT(ft.path, ',', r.id)
    FROM family_tree ft
    JOIN tbl_family_relationships fr ON fr.person_id = ft.id
    JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id
        AND rt.name IN ('wife', 'husband')
    JOIN tbl_registration r ON fr.related_person_id = r.id
    WHERE FIND_IN_SET(r.id, ft.path) = 0

    UNION ALL

    -- Find siblings (both directions)
    SELECT
        CASE WHEN fr.person_id = ft.id THEN fr.related_person_id ELSE fr.person_id END AS id,
        ft.generation,
        CONCAT(ft.path, ',', CASE WHEN fr.person_id = ft.id THEN fr.related_person_id ELSE fr.person_id END)
    FROM family_tree ft
    JOIN tbl_family_relationships fr ON
        (fr.person_id = ft.id OR fr.related_person_id = ft.id)
    JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id
        AND rt.name IN ('brother', 'sister')
    WHERE CASE WHEN fr.person_id = ft.id THEN fr.related_person_id ELSE fr.person_id END != ft.id
    AND FIND_IN_SET(CASE WHEN fr.person_id = ft.id THEN fr.related_person_id ELSE fr.person_id END, ft.path) = 0
)

SELECT
    ft.generation,
    r.id,
    CONCAT(r.first_name, ' ', r.middle_name, ' ', r.last_name) AS person_name,

    -- Father
    COALESCE((
        SELECT CONCAT(f.first_name, ' ', f.middle_name, ' ', f.last_name)
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name = 'father'
        JOIN tbl_registration f ON fr.related_person_id = f.id
        WHERE fr.person_id = r.id
    ), 'Not Found') AS father,

    -- Mother
    COALESCE((
        SELECT CONCAT(m.first_name, ' ', m.middle_name, ' ', m.last_name)
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name = 'mother'
        JOIN tbl_registration m ON fr.related_person_id = m.id
        WHERE fr.person_id = r.id
    ), 'Not Found') AS mother,

    -- Spouse
    COALESCE((
        SELECT CONCAT(s.first_name, ' ', s.middle_name, ' ', s.last_name)
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name IN ('wife', 'husband')
        JOIN tbl_registration s ON fr.related_person_id = s.id
        WHERE fr.person_id = r.id
    ), 'Not Found') AS spouse,

    -- Maiden name
    COALESCE((
        SELECT CONCAT(mn.maiden_first_name, ' ', mn.maiden_middle_name, ' ', mn.maiden_last_name)
        FROM tbl_maiden_name mn
        WHERE mn.registration_id = r.id
    ), 'Not Found') AS maiden_name,

    -- Corrected children list (only shows actual children)
    (
        SELECT GROUP_CONCAT(DISTINCT
            CONCAT(ch.first_name, ' ', ch.middle_name, ' ', ch.last_name)
            ORDER BY ch.id SEPARATOR ', '
        )
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.reverse_relation = 'parent'
        JOIN tbl_registration ch ON fr.person_id = ch.id
        WHERE fr.related_person_id = r.id
    ) AS children,

    -- Corrected siblings list (no duplicates)
    (
        SELECT GROUP_CONCAT(DISTINCT
            CONCAT(sib.first_name, ' ', sib.middle_name, ' ', sib.last_name)
            ORDER BY sib.id SEPARATOR ', '
        )
        FROM tbl_family_relationships fr
        JOIN tbl_relation_types rt ON fr.relation_type_id = rt.id AND rt.name IN ('brother', 'sister')
        JOIN tbl_registration sib ON
            (fr.person_id = r.id AND fr.related_person_id = sib.id) OR
            (fr.related_person_id = r.id AND fr.person_id = sib.id)
        WHERE sib.id != r.id
    ) AS siblings

FROM family_tree ft
JOIN tbl_registration r ON ft.id = r.id
GROUP BY r.id
ORDER BY ft.generation, r.id;

-- ----------------------------------
WITH RECURSIVE family_tree AS (
  -- Start with Virajsinh (ID 1)
  SELECT
    id,
    0 AS generation,
    CAST(id AS CHAR(1000)) AS path
  FROM registration
  WHERE id = 1

  UNION ALL

  -- Ancestors (parents)
  SELECT
    r.id,
    ft.generation - 1,
    CONCAT(ft.path, ',', r.id)
  FROM family_tree ft
  JOIN family_relationships fr
    ON fr.person_id = ft.id
  JOIN relation_types rt
    ON fr.relation_type_id = rt.id
     AND rt.name IN ('father', 'mother')
  JOIN registration r
    ON fr.related_person_id = r.id
  WHERE FIND_IN_SET(r.id, ft.path) = 0

  UNION ALL

  -- Descendants (children)
  SELECT
    r.id,
    ft.generation + 1,
    CONCAT(ft.path, ',', r.id)
  FROM family_tree ft
  JOIN family_relationships fr
    ON fr.related_person_id = ft.id
  JOIN relation_types rt
    ON fr.relation_type_id = rt.id
     AND rt.name IN ('father', 'mother')
  JOIN registration r
    ON fr.person_id = r.id
  WHERE FIND_IN_SET(r.id, ft.path) = 0

  UNION ALL

  -- Spouses
  SELECT
    r.id,
    ft.generation,
    CONCAT(ft.path, ',', r.id)
  FROM family_tree ft
  JOIN family_relationships fr
    ON fr.person_id = ft.id
  JOIN relation_types rt
    ON fr.relation_type_id = rt.id
     AND rt.name IN ('wife', 'husband')
  JOIN registration r
    ON fr.related_person_id = r.id
  WHERE FIND_IN_SET(r.id, ft.path) = 0

  UNION ALL

  -- Siblings (both directions)
  SELECT
    CASE WHEN fr.person_id = ft.id THEN fr.related_person_id ELSE fr.person_id END AS id,
    ft.generation,
    CONCAT(ft.path, ',', CASE WHEN fr.person_id = ft.id THEN fr.related_person_id ELSE fr.person_id END)
  FROM family_tree ft
  JOIN family_relationships fr
    ON fr.person_id = ft.id OR fr.related_person_id = ft.id
  JOIN relation_types rt
    ON fr.relation_type_id = rt.id
     AND rt.name IN ('brother', 'sister')
  WHERE CASE
          WHEN fr.person_id = ft.id THEN fr.related_person_id
          ELSE fr.person_id
        END <> ft.id
    AND FIND_IN_SET(
          CASE WHEN fr.person_id = ft.id THEN fr.related_person_id ELSE fr.person_id END,
          ft.path
        ) = 0
)

SELECT
  MIN(ft.generation) AS generation,
  r.id,
  CONCAT(r.first_name, ' ', r.middle_name, ' ', r.last_name) AS person_name,

  -- Father
  COALESCE(fa.name, 'Not Found') AS father,

  -- Mother
  COALESCE(mo.name, 'Not Found') AS mother,

  -- Spouse
  COALESCE(spouse.name, 'Not Found') AS spouse,

  -- Maiden Name
  COALESCE(mn.name, 'Not Found') AS maiden_name,

  -- Children (aggregated, both directions)
  COALESCE(children.names, 'Not Found') AS children,

  -- Siblings (aggregated)
  COALESCE(sibs.names, 'Not Found') AS siblings

FROM family_tree ft
JOIN registration r
  ON ft.id = r.id

-- Father lookup
LEFT JOIN (
  SELECT fr.person_id,
         CONCAT(f.first_name, ' ', f.middle_name, ' ', f.last_name) AS name
  FROM family_relationships fr
  JOIN relation_types rt
    ON fr.relation_type_id = rt.id
     AND rt.name = 'father'
  JOIN registration f
    ON fr.related_person_id = f.id
) AS fa ON fa.person_id = r.id

-- Mother lookup
LEFT JOIN (
  SELECT fr.person_id,
         CONCAT(m.first_name, ' ', m.middle_name, ' ', m.last_name) AS name
  FROM family_relationships fr
  JOIN relation_types rt
    ON fr.relation_type_id = rt.id
     AND rt.name = 'mother'
  JOIN registration m
    ON fr.related_person_id = m.id
) AS mo ON mo.person_id = r.id

-- Spouse lookup
LEFT JOIN (
  SELECT fr.person_id,
         CONCAT(s.first_name, ' ', s.middle_name, ' ', s.last_name) AS name
  FROM family_relationships fr
  JOIN relation_types rt
    ON fr.relation_type_id = rt.id
     AND rt.name IN ('wife', 'husband')
  JOIN registration s
    ON fr.related_person_id = s.id
) AS spouse ON spouse.person_id = r.id

-- Maiden name lookup
LEFT JOIN (
  SELECT mn.registration_id,
         CONCAT(mn.first_name, ' ', mn.middle_name, ' ', mn.last_name) AS name
  FROM maiden_name mn
) AS mn ON mn.registration_id = r.id

-- Children lookup (both directions: father/mother → child and son/daughter → parent)
LEFT JOIN (
  SELECT parent_id,
         GROUP_CONCAT(DISTINCT
           TRIM(
             CONCAT(
               CASE
                 WHEN c.gender = 2 AND mn.id IS NOT NULL THEN
                   CONCAT(mn.first_name, ' ', mn.middle_name, ' ', mn.last_name)
                 ELSE
                   CONCAT(c.first_name, ' ', c.middle_name, ' ', c.last_name)
               END
             )
           )
           ORDER BY c.id SEPARATOR ', '
         ) AS names
  FROM (
    SELECT fr.related_person_id AS parent_id, fr.person_id AS child_id
    FROM family_relationships fr
    JOIN relation_types rt
      ON fr.relation_type_id = rt.id
       AND rt.name IN ('father', 'mother')

    UNION ALL

    SELECT fr.person_id AS parent_id, fr.related_person_id AS child_id
    FROM family_relationships fr
    JOIN relation_types rt
      ON fr.relation_type_id = rt.id
       AND rt.name IN ('son', 'daughter')
  ) AS unioned
  JOIN registration c ON unioned.child_id = c.id
  LEFT JOIN maiden_name mn ON mn.registration_id = c.id
  GROUP BY parent_id
) AS children ON children.parent_id = r.id

-- Siblings lookup (aggregated with maiden name for females)
LEFT JOIN (
  SELECT s.person_id,
         GROUP_CONCAT(DISTINCT
           TRIM(
             CONCAT(
               CASE
                 WHEN sib.gender = 2 AND mn.id IS NOT NULL THEN
                   CONCAT(mn.first_name, ' ', mn.middle_name, ' ', mn.last_name)
                 ELSE
                   CONCAT(sib.first_name, ' ', sib.middle_name, ' ', sib.last_name)
               END
             )
           )
           ORDER BY sib.id SEPARATOR ', '
         ) AS names
  FROM (
    SELECT fr.related_person_id AS sib_id, fr.person_id AS person_id
    FROM family_relationships fr
    JOIN relation_types rt ON fr.relation_type_id = rt.id
    WHERE rt.name IN ('brother', 'sister')

    UNION ALL

    SELECT fr.person_id AS sib_id, fr.related_person_id AS person_id
    FROM family_relationships fr
    JOIN relation_types rt ON fr.relation_type_id = rt.id
    WHERE rt.name IN ('brother', 'sister')
  ) AS s
  JOIN registration sib ON sib.id = s.sib_id
  LEFT JOIN maiden_name mn ON mn.registration_id = sib.id
  GROUP BY s.person_id
) AS sibs ON sibs.person_id = r.id

GROUP BY r.id
ORDER BY generation, r.id;