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, AS all_id
    register AS r
    JOIN family AS fp
      ON =
    JOIN family AS fc
      ON fc.pathid LIKE CONCAT(fp.pathid, '%')
    fc.pathid ;