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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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
SELECT * FROM [dbo].[ufn_GetChildNodes] ( '0010' )
Yes, change the ID below to ManagerID:
FROM [dbo].[Operator_Master] A
WHERE [ManagerID] = @pManagerID
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
ASKER
stevetheski,
Will do, thanks so much
Will do, thanks so much
Bob