How to query designation hierarchy from a table having parent_id structure?

[sibani sahu]

Hi,

I want to generate the organisational structure of my company.I have a table /viewwhich has the emp_id,manager_id and the level_num.The level_num represents the designation level of the employee.So I used a query as:_

select concat(REPEAT('-',a.level_num *5),a.employee_name) as Org_Chart
from ALL_EMPLOYEE_VIEW A
order BY a.level_num ;

This does not create a tree structure but simply arranges the employees according to their levels or designations.However I want the nested set hierarchy that is all the employees be arranged under their manager in a hierarchical manner.Basically I want to create a managerial hierarchy as a organisation chart.How do I do that????????Do I have to query using a stored procedure/function as this is recursive?????If any body has a solution please reply asap.

[nbiswas]

Try this

Managing Hierarchical Data in MySQL

There just scroll down and in the Finding the Depth of the Nodes section you will find

SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
 

The output is similar to the one you have asked for.

Hope this helps