Chaining System Query in MySQL

Question

I have a table which maintains a user chain

Here is the table structure

+------+--------+
| id | user |
+------+--------+
| 2 | 4 |
| 10 | 4 |
| 11 | 4 |
| 1 | 2 |
| 3 | 10 |
| 4 | 5 |
| 34 | 1 |
| 35 | 11 |
+------+--------+

I have to write a query which will out the chain of users

For example : if I have to find chain of user '4' then result will be

2, 10, 11, 1,3,35, 34 

Answer 1

Your Question

What you are looking to do is achievable in SQL Server using CTE recursive queries. MySQL's dialect of SQL does not support such querying.

I do understand the data and the output. For what you are looking to do, this is the solution:

R : 4
NodeQueue: <Empty>
OutputString: <Empty>

Add R's Children to NodeQueue

R : 4
NodeQueue: 2,10,11
OutputString: <Empty>

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString

R : 2
NodeQueue: 10,11,1
OutputString: 2

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString

R : 10
NodeQueue: 11,1,3
OutputString: 2,10

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString

R : 11
NodeQueue: 1,3,35
OutputString: 2,10,11

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString

R : 1
NodeQueue: 3,35,34
OutputString: 2,10,11,1

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString

R : 3
NodeQueue: 35,34 (3 has no children to add)
OutputString: 2,10,11,1,3

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString

R : 35
NodeQueue: 34 (35 has no children to add)
OutputString: 2,10,11,1,3,35

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString

R : 34
NodeQueue: <Empty> (35 has no children to add)
OutputString: 2,10,11,1,3,35,34

Algorithm ends here because the NodeQueue became empty.

GOOD NEWS

I have already written MySQL Stored Functions that do this

• Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
• Dec 10, 2012 : MySQL: Tree-Hierarchical query

Please look over these over because the algorithm I described is in these two posts. You will have to decide what constitutes the Root to start with.

Give it a Try !!!