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_VI EW A
order BY a.level_num ;

This does not create a tree structure but simply arranges the employees according to their levels or designations.Ho wever I want the nested set hierarchy that is all the employees be arranged under their manager in a hierarchical manner.Basicall y 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?????I f any body has a solution please reply asap.

[nbiswas]

Solution to How to query designation hierarchy from a table having parent_idstruct ure

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