complex organization grouping model query

Hi,

One of the experts guided me to produce this design wherein we can query a tree model.

kindly assist in creating the sql to query all the operatorID under specific ManagerID
i.e.
ManagerID
0010

operator_master
OperatorID  ManagerID          
0001              0004
0002              0004    
0003              0004    
0004              0008  
0005              0007    
0006              0007    
0007              0008    
0008              0010      
0009              0007
0010              

details:
0010 has 1 member 0008
0008 has 2 memebers 0004, 0007
0004 has 3 memebers 0001, 0002, 0003
0007 has 3 members 0005, 0006, 0009

total of 9 ID's

Output:
operatorid:
0001
0002
0003
0004
0005
0006
0007
0008
0009


Thanks.
EdwardPeterAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
Here's a user-defined function that you can use.  It uses recursion and this function is limited to 32 levels of hierarchy only.

CREATE FUNCTION [dbo].[ufn_GetChildNodes] ( @pManagerID  CHAR(4) )
RETURNS @vReturnTable TABLE ( [OperatorID] CHAR(4) )
AS
BEGIN

    DECLARE cChildNodes CURSOR LOCAL FOR
        SELECT [OperatorID] FROM [dbo].[Operator_Master]
        WHERE [ManagerID] = @pManagerID  

    DECLARE @vChildNode            CHAR(4)

    INSERT INTO @vReturnTable ( [OperatorID] )
    SELECT OperatorID
    FROM [dbo].[Operator_Master] A
    WHERE [ID] = @pManagerID AND NOT EXISTS (SELECT 'X' FROM @vReturnTable B
                                                                          WHERE A.[OperatorID] = B.[OperatorID])

    OPEN cChildNodes
    FETCH NEXT FROM cChildNodes INTO @vChildNode
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @vReturnTable ( [OperatorID] )
        SELECT [OperatorID]
        FROM [dbo].[ufn_GetChildNodes] ( @vChildNode )

        FETCH NEXT FROM cChildNodes INTO @vChildNode
    END

    CLOSE cChildNodes
    DEALLOCATE cChildNodes

    RETURN

END
0
 
Bob LambersonSoftware EngineerCommented:
Select * from operator_master Where managerID = '0010'

Bob
0
 
EdwardPeterAuthor Commented:
rafrancisco,

Your a life saver!!!

Is it okay if you could kindly assist how do we execute the query you've post?

tried exec [dbo].[ufn_GetChildNodes]  0010  -generated an error

-
FROM [dbo].[Operator_Master] A
    WHERE [ID] = @pManagerID AND NOT EXISTS (SELECT 'X' FROM @vReturnTable B     <-------i've place managerID
                                                                                                                                      in the where part hope this is correct?

Thanks.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
stevetheskiCommented:
this is the same answer basically that i gave you yesterday and it is till the most widly used hierarchial method using sql  


CREATE PROC GetSubs (@ManagerId INT)
AS
   BEGIN
        CREATE TABLE #TEMPoperator_master (
          ManagerId int,
          operatorID int )
      EXEC ShowHierarchy @ManagerId;
      SELECT operatorId FROM #TEMPoperator_master
      WHERE operatorID != @ManagerId
      order by operatorID
      DROP TABLE #TEMPoperator_master
   END

GO


CREATE PROCEDURE dbo.ShowHierarchy
(
   @Root INT
)
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @operatorID INT

SET @operatorID = (SELECT MIN(operatorID) FROM dbo.operator_master2 WHERE ManagerId = @Root)
insert into #TEMPoperator_master
Select ManagerId, operatorID
FROM dbo.operator_master2 WHERE ManagerId = @Root

   WHILE @operatorID IS NOT NULL
   BEGIN
      EXEC dbo.ShowHierarchy @operatorID
      SET @operatorID = (SELECT MIN(operatorID) FROM dbo.operator_master2 WHERE ManagerId = @Root AND operatorID > @operatorID)
   END

END
GO

EXEC GetSubs 1 and 2 and 3 and 5 and 6 and 9
returns no results

EXEC GetSubs 4
returns
1,2,3

EXEC GetSubs 7
returns
5,6,9


EXEC GetSubs 8
returns
1,2,3,4,5,6,7,9 since 8 manages 4 and 7 by default he also manages their subordinates


EXEC GetSubs10
returns everyone since 10 manages 8 we get 8 and 8 subordinates

Glad to see you redesigned your tables

And dont wory about the error

annot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ShowHierarchy'. The stored procedure will still be created.

it is just a warning saying that ShowHierarchy hasnt been created yet. the key is the last sentence
The stored procedure will still be created.




0
 
rafranciscoCommented:
To execute it:

SELECT * FROM [dbo].[ufn_GetChildNodes] ( '0010' )

Yes, change the ID below to ManagerID:

FROM [dbo].[Operator_Master] A
    WHERE [ManagerID] = @pManagerID
0
 
stevetheskiConnect With a Mentor Commented:
EdwardPeter

the method that rafrancisco posted is a function that is to be used in a select statement.

so you would call it like so
select * from dbo.ufn_GetChildNodes(10);

the userids and managerids should definately be INT types
and then you should definately use the query I supplied vs the cursor.

snippet from
http://www.sql-server-performance.com/rd_interview.asp

Finally, cursors. If at all possible, avoid them. This can be a bit of a contentious issue, but compared to working with a table, they are very slow indeed. When building a stored procedure, try to work with temporary tables rather than a cursor. If you are using a cursor on a small number of records then performance won't be so bad, but there will be a major degradation. For large tables, try to find a way around it.


Steve
0
 
EdwardPeterAuthor Commented:
rafrancisco and stevetheski,

You guys are simple the BEST !!! thanks for your time and patience.


here's what i did using all your idea's to make it a reality:

using rafrancisco's kick ass function and then


using Steve's create view (great idea by steve)

create view operatormaster_teammaster as
select a.operatorid, b.supervisorID as managerid
from operator_master a
left join team_master b on a.teamid=b.teamid
left join operator_master c on b.supervisorid= c.operatorid
where b.supervisorid is not null


two tables
operator_master
operatorID OperatorName TeamID
0001                abc1              1
0002                abc2              1
0003                abc3              1
0004                abc4              3
0005                abc5              2
0006                abc6              2
0007                abc7              3
0008                abc8              
0009                abc9              2

team_master
TeamID TeamName SupervisorID (this is same as operatorID)
1                   team1         0004
2                   team2         0007
3                   team3         0008
0
 
stevetheskiCommented:
Edward,

I am tellling you using the above function may cause you problems in the long run.
if you really want to use a function

I would suggest this.

It is a good alternative to the recursive function.
Depending on which book you read it may be better.
I prefer the recursive procs myself used them for years and it is easy to follow.
But here is an example that you can use if you really want a function
if your columns are character the below function can be altered to support that , also support over 32 hierarchial levels which none of the previous examples will.  ( generally not needed )
note we must use table variables new to sql server 2000 as opposed to table variables
once you get your table up to a 100,000 rows try them all and analyze the performance
look for search vs seek in the execution plan
OK I CHEATED and ran to my office to grab the function below its not something ya write every day and once ya have it you dont need to mess with it :)

Create Function udf_ExpHier (@Manager int)
RETURNS
@subordinates TABLE (operatorID int)
AS
BEGIN
   DECLARE @lvl int, @current int
   DECLARE @Stack table (item int, lvl int)
   SELECT @current = @Manager

   INSERT INTO @Stack VALUES (@current, 1)
   SELECT @lvl = 1
   WHILE @lvl > 0
      BEGIN
         IF EXISTS (SELECT * FROM @Stack WHERE lvl = @lvl)
            BEGIN
               SELECT @current = item
               FROM @Stack
               WHERE lvl = @lvl
               
               INSERT INTO @subordinates values(@current)
            
               DELETE FROM @Stack
               WHERE lvl = @lvl
                  AND item = @current
               INSERT @Stack
                  SELECT operatorID, @lvl + 1
                  FROM operator_master2
                  WHERE ManagerId = @current
               IF @@ROWCOUNT > 0
                  SELECT @lvl = @lvl + 1
            END
         ELSE
            SELECT @lvl = @lvl - 1
   END -- WHILE
RETURN


END
go
select * from udf_ExpHier(7)
where operatorid != 7


Steve
0
 
EdwardPeterAuthor Commented:
stevetheski,

Will do, thanks so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.