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.
Table 2 (named as register) has the name column and the id column.
Required result is name with all the family descendants only.
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.
Something like this should do. When you have an hierarchical structure with the "path" stored, it’s often easier to use text comparisons, eg.
(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 🙂