0

Table 1 (named as family) has a tree structure with 2 columns id and pathid, where pathid describes the tree structure. In the following table 1 is a root node with 2 as its child. 3 is a rootnode with 4 as a child and 5 is the child of 4(grandchild of 3). 6 is a rootnode with no children. 7 is a rootnode with 3 children 8, 9 and 10.

id pathid parentid
1 /1
2 /1/2 1
3 /3
4 /3/4 3
5 /3/4/5 4
6 /6
7 /7
8 /7/8 7
9 /7/9 7
10 /7/10 7

Table 2 (named as register) has the name column and the id column.

name id
A 1
B 3
C 6
D 7
E 4
F 10

Required result is name with all the family descendants only.

name all_id
A 1
A 2
B 3
B 4
B 5
C 6
D 7
D 8
D 9
D 10
E 4
E 5
F 10
New contributor
mrtyormaa is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0
2

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 ;

Your Answer

mrtyormaa is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.