# 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 🙂