Joining a tree structure

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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 ;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply