Hierarchical Data in MySql
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 Us开发者_高级运维er 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
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
Query the table once, getting all the names and IDs, then construct the tree in whatever programming language you're using.
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
精彩评论