Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: Recusrive SQL help

    Hi,

    I have a table that has coms like below:

    ManagerID INT
    EmployeeID INT
    EmployeeName Varchar
    EmployeeEmail Varchar

    Now I have to create a stored proc to which I pass one Emp ID and it should get me all employee udenr him till last employee in the hierarchy.
    So if one manager has three sub managers and two employee and those three manager has 2 employees each, and if I pass the emp ID of the main manager, it should give me All EmpID under him and also under the sub mangers he has. And If I pass one of the sub managers, it should give me the EmpID of only those two who are under him.
    I hope I am making sens.

    I created a SP that uses a cursor and recursively calls itself. But it is not working as expected. I so not think I should use cursor here in the first place. Please help.
    Regards,
    Sandy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do a search for recursive CTE

    the employee hierarchy is a common example, you'll be able to find some actual code that you can use
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    4

    Ceursive SQl

    Actually I did go though them but none of them satisfy my requirement where the nesting level is not known.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    #1 result in google -- Recursive Queries Using Common Table Expressions
    Code:
    USE AdventureWorks;
    GO
    WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
    AS
    (
    -- Anchor member definition
        SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
            0 AS Level
        FROM HumanResources.Employee AS e
        INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
            ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
        WHERE ManagerID IS NULL
        UNION ALL
    -- Recursive member definition
        SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
            Level + 1
        FROM HumanResources.Employee AS e
        INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
            ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
        INNER JOIN DirectReports AS d
            ON e.ManagerID = d.EmployeeID
    )
    -- Statement that executes the CTE
    SELECT ManagerID, EmployeeID, Title, Level
    FROM DirectReports
    INNER JOIN HumanResources.Department AS dp
        ON DirectReports.DeptID = dp.DepartmentID
    WHERE dp.GroupName = N'Research and Development' OR Level = 0;
    GO
    #2 result in google -- SQL SERVER – Simple Example of Recursive CTE Journey to SQL Authority with Pinal Dave
    Code:
    USE AdventureWorks
    GO
    WITH Emp_CTE AS (
    SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
    FROM HumanResources.Employee e
    INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
    )
    SELECT *
    FROM Emp_CTE
    GO
    Last edited by r937; 03-04-10 at 17:50.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    4

    Recusrive SQl

    Please carefully look into these queris and my requirement. They fundamentally do not match. None f these will server my purpose nor will pave the way to derive one. I have looked into them before posting here.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, i think they do match

    do you have some sample data? i'd be happy to try to write the sql for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Sorry, Sandy416, but R937 is correct (as he usually is). The SQL below is almost identical to the second example he posted. I create a Hierarchy just as you described (plus more to show that only the correct rows are returned) that get the results you seem to be asking for.

    NOTE: This was developed on DB2 but the syntax is identical (except for the first CTE that 'creates' the EMP_TAB with rows to work with.
    Code:
    WITH EMP_TAB (ManID, EmpId, EmpName)
      AS (SELECT NULL, 'Z', 'Owner'                  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'Z' , 'A', 'Big Boss 1'               FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'A' , 'B', 'Big Boss 1 Little Boss 1' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'A' , 'C', 'Big Boss 1 Little Boss 2' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'A' , 'D', 'Big Boss 1 Little Boss 3' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'A' , 'E', 'Big Boss 1 Emp 1'         FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'A' , 'F', 'Big Boss 1 Emp 2'         FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'B' , 'G', 'Little Boss 1 Emp 1'      FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'B' , 'H', 'Little Boss 1 Emp 2'      FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'C' , 'I', 'Little Boss 2 Emp 1'      FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'C' , 'J', 'Little Boss 2 Emp 2'      FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'D' , 'K', 'Little Boss 3 Emp 1'      FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'D' , 'L', 'Little Boss 3 Emp 2'      FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'Z' , 'M', 'Big Boss 2'               FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'M' , 'N', 'Big Boss 2 Little Boss 1' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'M' , 'O', 'Big Boss 2 Little Boss 2' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'M' , 'P', 'Big Boss 2 Emp 1'         FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'N' , 'Q', 'Little Boss 1 Emp 1'      FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'O' , 'R', 'Little Boss 1 Emp 1'      FROM SYSIBM.SYSDUMMY1
         )
    
    -- Ignore the above CTE
    
       , HIER_TAB (LEVEL, ManId, EmpId, EmpName)
      AS (SELECT 1, ManId, EmpId, EmpName
          FROM EMP_TAB
          WHERE EmpiD = 'A'
            UNION ALL
          SELECT LEVEL + 1, EMP_TAB.ManId, EMP_TAB.EmpId, EMP_TAB.EmpName
          FROM EMP_TAB
             , HIER_TAB
          WHERE EMP_TAB.ManId = HIER_TAB.EmpID
            AND LEVEL <= 20
         )
    SELECT *
    FROM HIER_TAB
    The Manager you want has an Employee Id of 'A'. When you Enter this in the WHERE clause you get:
    Code:
    LEVEL       MANID EMPID EMPNAME                 
    ----------- ----- ----- ------------------------
              1 Z     A     Big Boss 1              
              2 A     B     Big Boss 1 Little Boss 1
              2 A     C     Big Boss 1 Little Boss 2
              2 A     D     Big Boss 1 Little Boss 3
              2 A     E     Big Boss 1 Emp 1        
              2 A     F     Big Boss 1 Emp 2        
              3 B     G     Little Boss 1 Emp 1     
              3 B     H     Little Boss 1 Emp 2     
              3 C     I     Little Boss 2 Emp 1     
              3 C     J     Little Boss 2 Emp 2     
              3 D     K     Little Boss 3 Emp 1     
              3 D     L     Little Boss 3 Emp 2
    This is a Manager (A) with 3 Managers (B, C, and D) and 2 Employees (E and F) under him/her.

    Each of the 3 Managers has 2 Employees each (G and H, I and J, and K and L).

    AS you can see only those rows were returned.

    If a B is used (one of the Second level managers you get:
    Code:
    LEVEL       MANID EMPID EMPNAME                 
    ----------- ----- ----- ------------------------
              1 A     B     Big Boss 1 Little Boss 1
              2 B     G     Little Boss 1 Emp 1     
              2 B     H     Little Boss 1 Emp 2
    This appears to get you what you are asking for.

    PS One difference is I added AND LEVEL <= 20. The 20 is an arbitrary number that ensures that the Recursive query doesn't go into an endless loop. Make the number large enough to handle any number of Levels needed but not so large that the SQL 'runs forever' before stopping (when you have an error).
    Last edited by Stealth_DBA; 03-04-10 at 20:14.

  8. #8
    Join Date
    Mar 2010
    Posts
    4

    Thanks a million

    It worked. And it was really simple. Thanks again

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •