?
Solved

complex organization grouping model query

Posted on 2005-05-08
9
Medium Priority
?
292 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:EdwardPeter
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1600 total points
ID: 13956230
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
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13956231
Select * from operator_master Where managerID = '0010'

Bob
0
 

Author Comment

by:EdwardPeter
ID: 13956263
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:stevetheski
ID: 13956348
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13956359
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
 
LVL 4

Assisted Solution

by:stevetheski
stevetheski earned 400 total points
ID: 13956362
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
 

Author Comment

by:EdwardPeter
ID: 13956390
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
 
LVL 4

Expert Comment

by:stevetheski
ID: 13956950
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
 

Author Comment

by:EdwardPeter
ID: 13966845
stevetheski,

Will do, thanks so much
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question