Hierarchical Data in MySql

Question

I have a table with parent child relation I want help on 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 recursive query but I am not able to do please suggest me how should I query database e.g.

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

Answer 1

Query the table once, getting all the names and IDs, then construct the tree in whatever programming language you're using.

Answer 2

As pointed out above this isn't truly recursive but if you know how many steps deep you need to go as a maximum, you can use something along these lines (perhaps use PHP to generate the query):

I'd first set parent ID to NULL rather than 0, but that's 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

^^ however deep you need to go in that case.

[next bit not strictly relevant]

You can then manipulate the output something along these lines:

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 3

MySQL directly does not support recursive queries.

You'll need to emulate it by writing a function that keeps the recursion stack in a session variable.

See this article in my blog on how to do this:

• Hierarchical queries in MySQL