postgresql – Joining a tree structure

Something like this should do. When you have an hierarchical structure with the “path” stored, it’s often easier to use text comparisons, eg. LIKE:
(When the path is stored as text. If it is stored as an array, you’d use array operators).

SELECT
    r.name,
    fc.id AS all_id
FROM
    register AS r
    JOIN family AS fp
      ON r.id = fp.id
    JOIN family AS fc
      ON fc.pathid LIKE CONCAT(fp.pathid, '%')
ORDER BY
    r.name, r.id,
    fc.pathid ;