Using CTE to get the hierarchy of emp regardless the position

write sql query for getting direct and indirect reportees of a given employee
sql query to get recursive count of employees under each manager
sql query to get organization hierarchy
employee manager hierarchy sql
sql server query to get hierarchical tree
employee manager hierarchy mysql
sql server parent child hierarchy
sql recursive query on self referencing table

I am looking into creating a hierarchy query using CTE. However, I am missing something on the where clause, the query should return the full hierarchy of an employee

Here is a SQL fiddle that I have created.

Expected results:

For id = 3 I should get these results:

ParentEmpId Id          Name 
----------- ----------- -----
NULL        1           A    
1           3           C    
3           6           F     

For id = 2, I should get these results:

ParentEmpId Id          Name 
----------- ----------- -----
NULL        1           A    
1           2           B    
2           4           D     
2           5           E     

From your expected output it seems that you need both children and parents of an id. Therefore, you need one hierarchy for children and one for parents:

WITH EmpCTE(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
    WHERE id=3
         UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE empCTE
        ON E.Id = EmpCTE.ParentEmpId 
),
 EmpCTE2(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
    WHERE id=3
         UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE2 empCTE2
        ON E.ParentEmpId = EmpCTE2.Id 
)
Select * from (
select * from EmpCTE 
Union 
select * from EmpCTE2 ) a
order by name

How to find a hierarchy of employees, 3 levels deep, using JOINS, 问题: I am looking into creating a hierarchy query using CTE. However, I am missing something on the where clause, the query should return� 2 Using CTE to get the hierarchy of emp regardless the position Mar 17 '19. 2 Sessions with SPA application and API Sep 12 '19. 2 Create and use database using t-sql

The current CTE that you are using generates the descending hierarchy of the given id, not the parent hierarchy.

One solution would be to create a second recursive CTE to generate the parent hierarchy, and then UNION both, as follows:

WITH EmpCTE(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
    WHERE id = 2
    UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE empCTE
        ON E.ParentEmpId = EmpCTE.id
),
EmpCTE2(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
    WHERE id = 2
    UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE2 empCTE2
        ON E.id = EmpCTE2.ParentEmpId
)
SELECT * FROM EmpCTE
UNION 
SELECT * FROM EmpCTE2
ORDER BY [name]

In this updated DB Fiddle, when given id = 2, the query returns:

ParentEmpId  Id  Name  Level
(null)       1   A     1
1            2   B     0
2            4   D     1
2            5   E     1

Stored Procedure- Multil level employee hierarchy in the same row, How to find a hierarchy of employees, 3 levels deep, using JOINS You can try LEFT JOIN instead of JOIN within a cte to get the managers/employees which are out of business. When someone gets a new job, change the org chart. An employee and all their Supervisors, no matter how deep the tree. What is a SQL Server Cursor. A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.

Why not this way?

select data.*
from emp e
  cross apply (
    select * 
    from emp e1
    where e1.Id=e.Id
    union all
    select *
    from emp e2
    where e2.ParentEmpId=e.Id
    union all
    select e4.*
    from emp e3
      join emp e4
        on e3.ParentEmpId=e4.Id
    where e3.Id=e.Id
  ) data
where e.Id=3

Employee Manager Hierarchy (CTE, I have an employee table where I have employee details which includes the following properties: With CTE AS ( SELECT EmployeeID,ManageriD, EmployeeName, 1 AS table which describes the Personnel that hold those positions. An employee and all their Supervisors, no matter how deep the tree. Thank you for reply, the idea is to get all employees who satisfies the condition (sal>=3000) and at the same time I want to know their managers of all levels regardless of manager's sal in a tree structure. the result I am looking for is like this: EMPNO name MGR SAL

SQL Server Graph Databases – Part 4: Working with Hierarchical , Now, we need to get the list of Names of manager hierarchy for employee - "MG". I hope you remember the "WITH" Common_Table_Expression. Seems incorrect, if I run the queries inside insert seperately I get correct results but if we use UNIONs we get data which we dont want, if we do it in two inserts we get duplicate rows One approach is this create a temp table using the delete statements but converted to querys for example instead of DELETE FROM OM_ANNEX t WHERE EXISTS (SELECT 1

Recursive Queries for Hierarchical Data Retrieval – TDAN.com, Working with hierarchical data is certainly not new in SQL Server. For example, an employee might work part time in two positions, which means other hand, are made for more complex hierarchies, regardless of the number of (If you have any questions about creating or populating graph tables, refer� If we don’t have an EMP it makes a great protective storage container in case of hurricanes. The Farraday Cage we are using to store the small electronics is a 20 gallon Galvanized Garbage Can lined with cardboard. There is a video on the Internet that shows how to make and test it using an FM radio.

SQL Server: Finding Top Level Item in Hierarchy, The employee table will have five columns: id, name, position, At the moment we just want to see CTE and recursive queries in action. To find hierarchical levels of employees, we will have to use an SQL expression.

Comments
  • Are you trying to get both children and parents of an id? because if you only need the hierarchy of parents of an id, the output would differ from your expected output.
  • There are a few different ways you could do this. Perhaps the simplest way would be to have one heirarchical CTE for an ID's children and one hierarchical CTE for an ID's parent(s) and then union the CTEs.
  • @rad: yes I am trying to get parents and children for a given I'd.
  • No, but it might be the simplest way. Others I can think of would require looping over a single CTE twice anyway (once to find anything related to an id and once to pull out the data). For example, you could add a column to the CTE that contains a comma-separated list of all the ids in the hierarchy sequence then do a string split on that to pull IDs then join back to the CTE (or the original table if you don't care about levels). There might be a way to make it more efficient than two CTEs, but two CTEs is more straight-forward IMO.
  • While my mind was wandering at lunch, I also considered that you could do this with the hierarchyid data type. For example, another possible solution can be found here: sqlfiddle.com/#!18/fee35/90 (though practically, unless your table is of a rather substantial size, it doesn't matter which method you go with in terms of performance - it's more about which is more readable/intuitive to you if you ever need to do maintenance).