Link to home
Start Free TrialLog in
Avatar of EdwardPeter
EdwardPeter

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Select * from operator_master Where managerID = '0010'

Bob
Avatar of EdwardPeter
EdwardPeter

ASKER

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.
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.




To execute it:

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

Yes, change the ID below to ManagerID:

FROM [dbo].[Operator_Master] A
    WHERE [ManagerID] = @pManagerID
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
stevetheski,

Will do, thanks so much