Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

SQL User Hierarchy

New Contributor II
New Contributor II

I'm seeking assistance on a non-recursive query to retrieve a list of employees spanning three hierarchical levels downward from the CEO. The current recursive query I have employs a Common Table Expression (CTE) and retrieves relevant details from the 'users' table. However, due to specific constraints, recursion cannot be used in Saviynt. The query is structured as follows:

WITH RECURSIVE EmployeeHierarchy AS ( SELECT email, username, manager, 1 AS level FROM users WHERE statuskey = 1 AND EMPLOYEETYPE = 'employee' AND MANAGER IS NULL AND JOBCODE = 'Chief Executive Officer' UNION ALL SELECT, e.username, e.manager, eh.level + 1 FROM users e JOIN EmployeeHierarchy eh ON e.manager = eh.username WHERE eh.level < 3 ) SELECT email, username, manager, level FROM EmployeeHierarchy;


I'm exploring alternative approaches for querying an employee hierarchy up to three levels deep. Currently, I'm using multiple LEFT JOINs on the 'employees' table to retrieve details such as employee ID, name, and hierarchy level. The query is structured as follows:

SELECT E.employee_id, COALESCE(E.employee_name, M1.employee_name, M2.employee_name, M3.employee_name) AS employee_name, CASE WHEN E.manager_id IS NULL THEN 0 WHEN M1.employee_id IS NOT NULL THEN 1 WHEN M2.employee_id IS NOT NULL THEN 2 WHEN M3.employee_id IS NOT NULL THEN 3 END AS level FROM employees AS E LEFT JOIN



I'm open to suggestions for a more efficient way to perform this operation. While the current requirement is to retrieve details from the CEO down three levels, future scenarios may involve querying details from a director down to the bottom of the hierarchy or something in the middle.
Writing joins to identify a certain hierarchy is not Ideal 

Any insights or recommendations would be greatly appreciated!!


Saviynt Employee
Saviynt Employee

Hi @Rayomand 

I appreciate you reaching out to Forums. 

Could you please confirm on which Database you are running the above queries. I am not able to see any employees or EmployeeHierarchy tables in EIC. 

Also please confirm what is the current requirement. Are you able to get the information but looking for query optimization? Are you facing any issue with the current query?


Dhruv Sharma

New Contributor II
New Contributor II

E.username, M1.username, M2.username, M3.username

user AS E
user AS M1 ON E.userkey = M1.manager
user AS M2 ON E.userkey = M2.manager
user AS M3 ON E.userkey = M3.manager;

I am querying via the data analyzer (the query in the original post was just an example of the format) above here is the  actual query.

I am getting the results, However If I need the CEO and all his reportees upto 3 levels then I need to do 3 joins with the users table.
If I need a department head and all users down to the bottom of the hierarchy I will have to do 'n' self joins.

Since saviynt does not support recursive querying using "with"
 what would be the best to query such data ?