Hierarchical data in MySql

Question

I have a table with a parent relationship of descendants. I need help with a recursive query

Table structure

roleId, roleName, parentId 1 Admin 0 2 Backup Admin 1 3 Office User 1 0 4 User 1 3 5 User 2 3 6 Office User 2 0 7 Off User 1 6 

I am trying to make a recursive query, but I can’t do it please suggest me how should I query the database for example

  Admin -- Backup Admin Office User 1 -- User 1 -- User 2 Office User 2 -- Off User 1 

Answer 1

As stated above, this is not recursive, but if you know how many steps you need to take the maximum, you can use something in these lines (perhaps using PHP to generate the query):

I would first set the parent id to NULL, not 0, but this is a personal preference.

 SELECT * FROM table t1 LEFT JOIN table t2 ON t2.parent_id = t1.role_id LEFT JOIN table t3 ON t3.parent_id = t2.role_id WHERE t1.parent_id IS NULL 

^^ No matter how deep you need to go in this case.

[ next bit is not strictly relevant ]

Then you can manipulate the output as follows:

 SELECT (CASE WHEN (t1.name IS NULL AND t2.name IS NULL) THEN t3.name WHEN (t1.name IS NULL AND t2.name IS NOT NULL) THEN t2.name ELSE t1.name END) AS first, (CASE WHEN (t1.name IS NOT NULL AND t2.name IS NOT NULL) THEN t2.name WHEN (t2.name IS NULL AND t3.name IS NOT NULL) THEN NULL ELSE t3.name END) AS second, (CASE WHEN (t1.name IS NOT NULL) THEN t3.name ELSE NULL END) AS third FROM 

Answer 2

Query the table once, get all the names and identifiers, and then build a tree in any programming language that you use.

Answer 3

MySQL does not directly support recursive queries.

You will need to emulate it by writing a function that stores the recursion stack in a session variable.

Check out this blog post on how to do this:

• Hierarchical queries in MySQL