locked
Infinite Loop RRS feed

  • Question

  • Hi Guys.,
    Table structure :

    create table tbemployee(
    empid varchar(1200,
    Name varchar(220),
    supervisor_id varchar(120)
    )

    we need get the employee levels
    Example:
    --1
    ---2
    ----3
    -----4
    Like this we are tried some query but it's loop continually

    Query :

    WITH Managers AS
    (
    --initialization
    SELECT EmpID,supervisor_ID
    FROM tblEmployee
    WHERE supervisor_ID = '286593'
    UNION ALL
    --recursive execution
    SELECT e.empid,e.supervisor_ID
    FROM tblEmployee e INNER JOIN Managers m
    ON e.supervisor_ID = m.empid
    )
    select * from managers

    please guide us to reslove this issue thanks for advance

    Thanks


    Ranganathan.Palanisamy
    Thursday, March 10, 2011 7:52 AM

Answers

  • Hello,

    Without have the data it's difficult to solve this, so I have to guess. The "Initialization" aka root, shouldn't it be the Supervisor itself? => empid instead suversiorid. And you may should filter out the "root" in the "childrens".

    As I said, just a guess:

    WITH Managers AS

    (

         --initialization

         SELECT EmpID,supervisor_ID

         FROM tblEmployee

         WHERE empid = '286593'

         UNION ALL

         --recursive execution

         SELECT e.empid,e.supervisor_ID

         FROM tblEmployee e INNER JOIN Managers m

         ON e.supervisor_ID = m.empid

         WHERE e.empid <> '286593'

    )

    select * from managers

     


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Thursday, March 10, 2011 8:09 AM

All replies

  • Hello,

    Without have the data it's difficult to solve this, so I have to guess. The "Initialization" aka root, shouldn't it be the Supervisor itself? => empid instead suversiorid. And you may should filter out the "root" in the "childrens".

    As I said, just a guess:

    WITH Managers AS

    (

         --initialization

         SELECT EmpID,supervisor_ID

         FROM tblEmployee

         WHERE empid = '286593'

         UNION ALL

         --recursive execution

         SELECT e.empid,e.supervisor_ID

         FROM tblEmployee e INNER JOIN Managers m

         ON e.supervisor_ID = m.empid

         WHERE e.empid <> '286593'

    )

    select * from managers

     


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Thursday, March 10, 2011 8:09 AM
  • Hi,

    Take a look at the microsoft example: http://msdn.microsoft.com/en-us/library/ms186243.aspx

     

    create table tblEmployee(
    empid varchar(1200),
    [Name] varchar(220),
    supervisor_id varchar(120)
    )
    
    INSERT INTO dbo.tblEmployee VALUES 
     (286593, N'AAA',NULL)
    ,(1, N'BBB',286593)
    ,(2, N'CCC',286593)
    ,(3, N'DDD',2)
    
    WITH Managers (EmpID, supervisor_ID, Level)
    AS 
    ( 
    --initialization 
    SELECT EmpID,supervisor_ID,0 AS Level
    FROM tblEmployee
    WHERE supervisor_ID is null
    UNION ALL
    --recursive execution 
    SELECT e.empid,e.supervisor_ID,Level + 1
    FROM tblEmployee e INNER JOIN Managers m 
    ON e.supervisor_ID = m.empid 
    ) 
    select * from managers
    
    

    Jon
    Thursday, March 10, 2011 8:21 AM