Hierarcy Query Mysql [duplicate]

Question

I have a table like this...

==========================================================================
UID    ||      PARENT ID           ||  SPONSOR ID
==========================================================================
1      ||         0                ||     0
2      ||         1                ||     1
3      ||         1                ||     1
4      ||         2                ||     1
5      ||         2                ||     2
6      ||         4                ||     1
7      ||         0                ||     0
8      ||         7                ||     7

Lets say I am a user with UID = 1, now I want to query so I can get all of my nodes inside my network, how to query this table so I can get this result:

============================================================================
UID     ||        PARENT ID          || SPONSOR
============================================================================
2       ||          1                || 1
3       ||          1                || 1
4       ||          2                || 1
5       ||          2                || 2
6       ||          4                || 1       

I try to using this query

select @pv:=uid as uid, parentid,sponsorid from genealogy 
join
(select @pv:=1)tmp
where parentid=@pv

But the result is only

============================================================================
UID     ||        PARENT ID          || SPONSOR
============================================================================
2       ||          1                || 1
4       ||          2                || 1
6       ||          4                || 1

Whats wrong with my query.. anyone can help me? Thankyou very much

Answer 1

If the depth of tree is limited to 3 (case of your sample). You can do like this.

(SELECT t1.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1) 
UNION
(SELECT t2.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1)
UNION
(SELECT t3.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1);

My solution don't respect DRY principle but this is the only simple solution that I found for now.

If you don't know in advance the maximum depth of your tree please check following questions and links that discuss the subject :

• https://stackoverflow.com/questions/5688376/mysql-select-tree-parent-ids
• https://stackoverflow.com/questions/18083546/mysql-recursivetree-parent-child-category
• http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Hope this help!

Answer 2

Before recursive CTEs were implemented in the Mysql 8+ hierarcies been based on a bit weird concepts like "Nested sets". There is no way to make a recursive queries in the earlier versions of the MySQL. Sure you are always able to make a stored routine with compound statements that implements any kind of recursion you want.