SQL Server CTE Bottom to Top Recursive with Where clause












3















I have an Employee Table with an EmployeeId, ManagerId and a Name field.



The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).



I found this link which helped to get the base of the code but I do not manage to make it work for my case



DECLARE @EmployeeTable table ([EmployeeId] int, [name] varchar(10), [managerId] int)
INSERT @EmployeeTable VALUES (1,'Jerome', NULL ) -- tree is as follows:
INSERT @EmployeeTable VALUES (2,'Joe' ,1) -- 1-Jerome
INSERT @EmployeeTable VALUES (3,'Paul' ,2) -- /
INSERT @EmployeeTable VALUES (4,'Jack' ,3) -- 2-Joe 9-Bill
INSERT @EmployeeTable VALUES (5,'Daniel',3) -- /
INSERT @EmployeeTable VALUES (6,'David' ,2) -- 3-Paul 6-David 10-Sam
INSERT @EmployeeTable VALUES (7,'Ian' ,6) -- / /
INSERT @EmployeeTable VALUES (8,'Helen' ,6) -- 4-Jack 5-Daniel 7-Ian 8-Helen
INSERT @EmployeeTable VALUES (9,'Bill ' ,1) --
INSERT @EmployeeTable VALUES (10,'Sam' ,9) --

DECLARE @employeeId int = 3

;WITH StaffTree AS
(
SELECT
c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
FROM @EmployeeTable c
LEFT OUTER JOIN @EmployeeTable cc ON c.managerId=cc.EmployeeId
WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
UNION ALL
SELECT
s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
FROM StaffTree t
INNER JOIN @EmployeeTable s ON t.[EmployeeId]=s.managerId
WHERE s.managerId=@employeeId OR @employeeId IS NULL OR t.Level>1
)
SELECT * FROM StaffTree


In case you select the employee 3 hierarchy, the result should be:



EmployeeId | Name    | ManagerId 
1 | Jerome | NULL
2 | Joe | 1
3 | Paul | 2









share|improve this question



























    3















    I have an Employee Table with an EmployeeId, ManagerId and a Name field.



    The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).



    I found this link which helped to get the base of the code but I do not manage to make it work for my case



    DECLARE @EmployeeTable table ([EmployeeId] int, [name] varchar(10), [managerId] int)
    INSERT @EmployeeTable VALUES (1,'Jerome', NULL ) -- tree is as follows:
    INSERT @EmployeeTable VALUES (2,'Joe' ,1) -- 1-Jerome
    INSERT @EmployeeTable VALUES (3,'Paul' ,2) -- /
    INSERT @EmployeeTable VALUES (4,'Jack' ,3) -- 2-Joe 9-Bill
    INSERT @EmployeeTable VALUES (5,'Daniel',3) -- /
    INSERT @EmployeeTable VALUES (6,'David' ,2) -- 3-Paul 6-David 10-Sam
    INSERT @EmployeeTable VALUES (7,'Ian' ,6) -- / /
    INSERT @EmployeeTable VALUES (8,'Helen' ,6) -- 4-Jack 5-Daniel 7-Ian 8-Helen
    INSERT @EmployeeTable VALUES (9,'Bill ' ,1) --
    INSERT @EmployeeTable VALUES (10,'Sam' ,9) --

    DECLARE @employeeId int = 3

    ;WITH StaffTree AS
    (
    SELECT
    c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
    FROM @EmployeeTable c
    LEFT OUTER JOIN @EmployeeTable cc ON c.managerId=cc.EmployeeId
    WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
    UNION ALL
    SELECT
    s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
    FROM StaffTree t
    INNER JOIN @EmployeeTable s ON t.[EmployeeId]=s.managerId
    WHERE s.managerId=@employeeId OR @employeeId IS NULL OR t.Level>1
    )
    SELECT * FROM StaffTree


    In case you select the employee 3 hierarchy, the result should be:



    EmployeeId | Name    | ManagerId 
    1 | Jerome | NULL
    2 | Joe | 1
    3 | Paul | 2









    share|improve this question

























      3












      3








      3


      1






      I have an Employee Table with an EmployeeId, ManagerId and a Name field.



      The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).



      I found this link which helped to get the base of the code but I do not manage to make it work for my case



      DECLARE @EmployeeTable table ([EmployeeId] int, [name] varchar(10), [managerId] int)
      INSERT @EmployeeTable VALUES (1,'Jerome', NULL ) -- tree is as follows:
      INSERT @EmployeeTable VALUES (2,'Joe' ,1) -- 1-Jerome
      INSERT @EmployeeTable VALUES (3,'Paul' ,2) -- /
      INSERT @EmployeeTable VALUES (4,'Jack' ,3) -- 2-Joe 9-Bill
      INSERT @EmployeeTable VALUES (5,'Daniel',3) -- /
      INSERT @EmployeeTable VALUES (6,'David' ,2) -- 3-Paul 6-David 10-Sam
      INSERT @EmployeeTable VALUES (7,'Ian' ,6) -- / /
      INSERT @EmployeeTable VALUES (8,'Helen' ,6) -- 4-Jack 5-Daniel 7-Ian 8-Helen
      INSERT @EmployeeTable VALUES (9,'Bill ' ,1) --
      INSERT @EmployeeTable VALUES (10,'Sam' ,9) --

      DECLARE @employeeId int = 3

      ;WITH StaffTree AS
      (
      SELECT
      c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
      FROM @EmployeeTable c
      LEFT OUTER JOIN @EmployeeTable cc ON c.managerId=cc.EmployeeId
      WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
      UNION ALL
      SELECT
      s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
      FROM StaffTree t
      INNER JOIN @EmployeeTable s ON t.[EmployeeId]=s.managerId
      WHERE s.managerId=@employeeId OR @employeeId IS NULL OR t.Level>1
      )
      SELECT * FROM StaffTree


      In case you select the employee 3 hierarchy, the result should be:



      EmployeeId | Name    | ManagerId 
      1 | Jerome | NULL
      2 | Joe | 1
      3 | Paul | 2









      share|improve this question














      I have an Employee Table with an EmployeeId, ManagerId and a Name field.



      The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).



      I found this link which helped to get the base of the code but I do not manage to make it work for my case



      DECLARE @EmployeeTable table ([EmployeeId] int, [name] varchar(10), [managerId] int)
      INSERT @EmployeeTable VALUES (1,'Jerome', NULL ) -- tree is as follows:
      INSERT @EmployeeTable VALUES (2,'Joe' ,1) -- 1-Jerome
      INSERT @EmployeeTable VALUES (3,'Paul' ,2) -- /
      INSERT @EmployeeTable VALUES (4,'Jack' ,3) -- 2-Joe 9-Bill
      INSERT @EmployeeTable VALUES (5,'Daniel',3) -- /
      INSERT @EmployeeTable VALUES (6,'David' ,2) -- 3-Paul 6-David 10-Sam
      INSERT @EmployeeTable VALUES (7,'Ian' ,6) -- / /
      INSERT @EmployeeTable VALUES (8,'Helen' ,6) -- 4-Jack 5-Daniel 7-Ian 8-Helen
      INSERT @EmployeeTable VALUES (9,'Bill ' ,1) --
      INSERT @EmployeeTable VALUES (10,'Sam' ,9) --

      DECLARE @employeeId int = 3

      ;WITH StaffTree AS
      (
      SELECT
      c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
      FROM @EmployeeTable c
      LEFT OUTER JOIN @EmployeeTable cc ON c.managerId=cc.EmployeeId
      WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
      UNION ALL
      SELECT
      s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
      FROM StaffTree t
      INNER JOIN @EmployeeTable s ON t.[EmployeeId]=s.managerId
      WHERE s.managerId=@employeeId OR @employeeId IS NULL OR t.Level>1
      )
      SELECT * FROM StaffTree


      In case you select the employee 3 hierarchy, the result should be:



      EmployeeId | Name    | ManagerId 
      1 | Jerome | NULL
      2 | Joe | 1
      3 | Paul | 2






      sql-server cte hierarchy recursive






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Feb 12 at 15:04









      Lenny32Lenny32

      1184




      1184






















          3 Answers
          3






          active

          oldest

          votes


















          2














          Swapping the columns in the inner join in the recursive part is a way to go about this.



          Join columns changed from t.[EmployeeId]=s.managerId to s.[EmployeeId]=t.managerId



          I deleted some parts that did not seem like they were needed.



          DECLARE @employeeId int = 3

          ;WITH StaffTree AS
          (
          SELECT
          c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
          FROM @EmployeeTable c
          WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
          UNION ALL
          SELECT
          s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
          FROM StaffTree t
          INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId
          )
          SELECT EmployeeId,
          name,
          managerId
          FROM StaffTree
          ORDER BY managerId asc;


          Result



          EmployeeId  name    managerId
          1 Jerome NULL
          2 Joe 1
          3 Paul 2





          share|improve this answer

































            0














            It looks like you had a tiny error when copying the example that you linked to -



            c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]


            should be



            c.[EmployeeId], c.[name], c.managerId, 1 AS [Level]


            Once I make that change, your code functions the same way as the example.






            share|improve this answer































              0














              This Link will help you a lot in learning Recursive CTEs with excellent and useful examples : https://www.codeproject.com/Articles/818694/SQL-queries-to-manage-hierarchical-or-parent-child.






              share|improve this answer























                Your Answer








                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "182"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                autoActivateHeartbeat: false,
                convertImagesToLinks: false,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: null,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229515%2fsql-server-cte-bottom-to-top-recursive-with-where-clause%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                3 Answers
                3






                active

                oldest

                votes








                3 Answers
                3






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes









                2














                Swapping the columns in the inner join in the recursive part is a way to go about this.



                Join columns changed from t.[EmployeeId]=s.managerId to s.[EmployeeId]=t.managerId



                I deleted some parts that did not seem like they were needed.



                DECLARE @employeeId int = 3

                ;WITH StaffTree AS
                (
                SELECT
                c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
                FROM @EmployeeTable c
                WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
                UNION ALL
                SELECT
                s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
                FROM StaffTree t
                INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId
                )
                SELECT EmployeeId,
                name,
                managerId
                FROM StaffTree
                ORDER BY managerId asc;


                Result



                EmployeeId  name    managerId
                1 Jerome NULL
                2 Joe 1
                3 Paul 2





                share|improve this answer






























                  2














                  Swapping the columns in the inner join in the recursive part is a way to go about this.



                  Join columns changed from t.[EmployeeId]=s.managerId to s.[EmployeeId]=t.managerId



                  I deleted some parts that did not seem like they were needed.



                  DECLARE @employeeId int = 3

                  ;WITH StaffTree AS
                  (
                  SELECT
                  c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
                  FROM @EmployeeTable c
                  WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
                  UNION ALL
                  SELECT
                  s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
                  FROM StaffTree t
                  INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId
                  )
                  SELECT EmployeeId,
                  name,
                  managerId
                  FROM StaffTree
                  ORDER BY managerId asc;


                  Result



                  EmployeeId  name    managerId
                  1 Jerome NULL
                  2 Joe 1
                  3 Paul 2





                  share|improve this answer




























                    2












                    2








                    2







                    Swapping the columns in the inner join in the recursive part is a way to go about this.



                    Join columns changed from t.[EmployeeId]=s.managerId to s.[EmployeeId]=t.managerId



                    I deleted some parts that did not seem like they were needed.



                    DECLARE @employeeId int = 3

                    ;WITH StaffTree AS
                    (
                    SELECT
                    c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
                    FROM @EmployeeTable c
                    WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
                    UNION ALL
                    SELECT
                    s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
                    FROM StaffTree t
                    INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId
                    )
                    SELECT EmployeeId,
                    name,
                    managerId
                    FROM StaffTree
                    ORDER BY managerId asc;


                    Result



                    EmployeeId  name    managerId
                    1 Jerome NULL
                    2 Joe 1
                    3 Paul 2





                    share|improve this answer















                    Swapping the columns in the inner join in the recursive part is a way to go about this.



                    Join columns changed from t.[EmployeeId]=s.managerId to s.[EmployeeId]=t.managerId



                    I deleted some parts that did not seem like they were needed.



                    DECLARE @employeeId int = 3

                    ;WITH StaffTree AS
                    (
                    SELECT
                    c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
                    FROM @EmployeeTable c
                    WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND c.managerId IS NULL)
                    UNION ALL
                    SELECT
                    s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
                    FROM StaffTree t
                    INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId
                    )
                    SELECT EmployeeId,
                    name,
                    managerId
                    FROM StaffTree
                    ORDER BY managerId asc;


                    Result



                    EmployeeId  name    managerId
                    1 Jerome NULL
                    2 Joe 1
                    3 Paul 2






                    share|improve this answer














                    share|improve this answer



                    share|improve this answer








                    edited Feb 12 at 16:18









                    Josh Darnell

                    6,46122038




                    6,46122038










                    answered Feb 12 at 15:41









                    Randi VertongenRandi Vertongen

                    3,479822




                    3,479822

























                        0














                        It looks like you had a tiny error when copying the example that you linked to -



                        c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]


                        should be



                        c.[EmployeeId], c.[name], c.managerId, 1 AS [Level]


                        Once I make that change, your code functions the same way as the example.






                        share|improve this answer




























                          0














                          It looks like you had a tiny error when copying the example that you linked to -



                          c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]


                          should be



                          c.[EmployeeId], c.[name], c.managerId, 1 AS [Level]


                          Once I make that change, your code functions the same way as the example.






                          share|improve this answer


























                            0












                            0








                            0







                            It looks like you had a tiny error when copying the example that you linked to -



                            c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]


                            should be



                            c.[EmployeeId], c.[name], c.managerId, 1 AS [Level]


                            Once I make that change, your code functions the same way as the example.






                            share|improve this answer













                            It looks like you had a tiny error when copying the example that you linked to -



                            c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]


                            should be



                            c.[EmployeeId], c.[name], c.managerId, 1 AS [Level]


                            Once I make that change, your code functions the same way as the example.







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Feb 12 at 15:57









                            Darren GDarren G

                            1




                            1























                                0














                                This Link will help you a lot in learning Recursive CTEs with excellent and useful examples : https://www.codeproject.com/Articles/818694/SQL-queries-to-manage-hierarchical-or-parent-child.






                                share|improve this answer




























                                  0














                                  This Link will help you a lot in learning Recursive CTEs with excellent and useful examples : https://www.codeproject.com/Articles/818694/SQL-queries-to-manage-hierarchical-or-parent-child.






                                  share|improve this answer


























                                    0












                                    0








                                    0







                                    This Link will help you a lot in learning Recursive CTEs with excellent and useful examples : https://www.codeproject.com/Articles/818694/SQL-queries-to-manage-hierarchical-or-parent-child.






                                    share|improve this answer













                                    This Link will help you a lot in learning Recursive CTEs with excellent and useful examples : https://www.codeproject.com/Articles/818694/SQL-queries-to-manage-hierarchical-or-parent-child.







                                    share|improve this answer












                                    share|improve this answer



                                    share|improve this answer










                                    answered Feb 13 at 11:27









                                    Pantea TourangPantea Tourang

                                    83




                                    83






























                                        draft saved

                                        draft discarded




















































                                        Thanks for contributing an answer to Database Administrators Stack Exchange!


                                        • Please be sure to answer the question. Provide details and share your research!

                                        But avoid



                                        • Asking for help, clarification, or responding to other answers.

                                        • Making statements based on opinion; back them up with references or personal experience.


                                        To learn more, see our tips on writing great answers.




                                        draft saved


                                        draft discarded














                                        StackExchange.ready(
                                        function () {
                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229515%2fsql-server-cte-bottom-to-top-recursive-with-where-clause%23new-answer', 'question_page');
                                        }
                                        );

                                        Post as a guest















                                        Required, but never shown





















































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown

































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown







                                        Popular posts from this blog

                                        Probability when a professor distributes a quiz and homework assignment to a class of n students.

                                        Aardman Animations

                                        Are they similar matrix