Start Free Trial

asked on

recursive query

Hi,

Three tables below:

designation_master
designationID designation organizationlevel
1 Director 4
2 Asst direcor 3
3 Manager 2
4 Supervisor 1
5 employee 0

operator_master
operatorID OperatorName TeamID designationID
0001 abc1 1 0<-employee
0002 abc2 1 0<-employee
0003 abc3 1 0<-employee
0004 abc4 3 4 <---supervisor
0005 abc5 2 0<-employee
0006 abc6 2 0<-employee
0007 abc7 3 4 <---supervisor
0008 abc8 3 <---manager
0009 abc9 2 0<-employee

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

output columns:
operatorID operatorName

Kindly assist how can we query all the memebers for a specific position in the organization chart.
i.e.
query all employee under 0008 (which is a manager).

Presently we can only query 1 level deep groupings, this will show all employee members of every team.

select a.operatorid, a.operatorname, b.teamName, b.SupervisorID, c.operatorname
from operator_master a
left join team_master b on a.teamid=b.teamid
left join operator_master c on b.supervisorid= c.operatorid

Thanks

you can do a self join on the table

SELECT child.*
FROM
operator_master AS child
,operator_master AS Parent
WHERE
child.designationID < Parent.designationID
and Parent.operatorID = 0008

ASKER

stevetheski,

Hmm, I may have lost you or you may have lost me, i'm not sure which..

But we can see that your query is only using 2 tables, how can we tell if who are the employees under 0008 ?

I though the team_master played a big role

Thanks.

ASKER

stevetheski,

oh my, I've wrongly paste the info..kindly see below for the correct structure.

designation_master
designationID designation organizationlevel
1 Director 4
2 Asst direcor 3
3 Manager 2
4 Supervisor 1
5 employee 0

operator_master
operatorID OperatorName TeamID designationID
0001 abc1 1 5<-employee <--------------should have use degignation ID instead of orglevel
0002 abc2 1 5<-employee
0003 abc3 1 5<-employee
0004 abc4 3 4 <---supervisor
0005 abc5 2 5<-employee
0006 abc6 2 5<-employee
0007 abc7 3 4 <---supervisor
0008 abc8 3 <---manager
0009 abc9 2 5<-employee

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

ok
so your employees have a greater ID than their managers
same query just make the LessThan a GreaterThan
what happens is we do a self join on the table

Making the PArent Table consist of the rows where the OperatorId = 8000 that is 1 row
so that row contains a designation id of 3 and we now need to get all of the children (those with a desId > 3)

so its the same thing

SELECT child.*
FROM
operator_master AS child
,operator_master AS Parent
WHERE
child.designationID > Parent.designationID
and Parent.operatorID = 0008

SELECT child.operatorID, Lookups.designation, team_master.TeamName
FROM
operator_master AS child,
operator_master AS Parent,
designation_master AS Lookups,
team_master
WHERE
child.designationID > Parent.designationID
and Parent.operatorID = 0008
and lookups.designationID = Child.designationID
and team_master.TeamId = Parent.TeamId

ASKER

stevetheski,

Amazing query, sadly you've made me realize the mistake on designation table.

There are other groups/organization
i.e.

Director
Ass Director
Manager
Supervisor
employee

-new group-
QIT Director
Qit Supervisor
Qit employee

-new group-
QA Director
Qa supervisor
Qit employee
...etc

Which table do we need to redesign in order to get the a specific top level food chain and query all the employees under him ?

Thanks.

ASKER

stevetheski,

Maybe we can query the supervisor of supervisor, meaning if the operatorID given to us has members then list all the memeber and if those has members then list again...

until we get the the last... using team_master table
>>
select a.operatorid, a.operatorname, b.teamName, b.SupervisorID, c.operatorname
from operator_master a
left join team_master b on a.teamid=b.teamid
left join operator_master c on b.supervisorid= c.operatorid
<<

hope this doesn't confuse you (this is a maybe)

Thanks.

ASKER

stevetheski,

The orgchart would grow and there will be lots of different teams, teamA supervisor of team A manager of Team A...etc

sample flow of the idea posted earlier, i'm not sure this is possible ...

0008 would list
0007 abc7 3 4 <---supervisor
0004 abc4 3 4 <---supervisor

and 0007 would list
0005 abc5 2 5<-employee
0006 abc6 2 5<-employee
0009 abc9 2 5<-employee

there won't be anything for 0005, 0006, 0009
but there will be employees for 0004
0001 abc1 1 5<-employee
0002 abc2 1 5<-employee
0003 abc3 1 5<-employee

nothing follows

hope this would help us..in listing all the employees under all the asst director/manager/supersivor/ etc

SELECT Parent.operatorID,child.operatorID, Lookups.designation, team_master.TeamName
FROM
operator_master AS child,
operator_master AS Parent,
designation_master AS Lookups,
team_master
WHERE
child.designationID = (Parent.designationID+1)
and lookups.designationID = Child.designationID
and team_master.TeamId = Parent.TeamId
AND PARENT.TEAMID = CHILD.TEAMID

go

SELECT child.operatorID, Lookups.designation, team_master.TeamName
FROM
operator_master AS child,
operator_master AS Parent,
designation_master AS Lookups,
team_master
WHERE
child.designationID = (Parent.designationID +1)
and Parent.operatorID = 0008
and lookups.designationID = Child.designationID
and team_master.TeamId = Parent.TeamId

ASKER

stevetheski,

There's a flaw with the designation table,

i.e.
designation_master tells us what are the employees rank.
ID designation
1 Director
2 Asst Director
3 Manager
4 Supervisor
5 employee
6 QA
7 emd
8 support
9 maintenance
10 qit

maybe you could kindly advice what we can do inorder to use the query you've posted.

I may be wrong here.

ASKER

I means employees designation not employees rank.

CREATE TABLE [dbo].[ReflexiveJoin] (
      [UserId] [int] NULL ,
      [UserName] [varchar] (50) NULL ,
      [ManagerId] [int] NULL ,
      [Team] [int] NULL
) ON [PRIMARY]
go
select --users for 'pebbles'
* from (select users.*
      from reflexivejoin manager, reflexivejoin users
      where manager.userid = users.managerid
      and manager.team = 2) as children
where children.managerid in
(select top 1 userid
from reflexivejoin
where username= 'pebbles')

there is alot more you can do with the queries for usability and tuning but this gives you the jist of reflexive joins AKA self joins

DATA--
UserId      UserName      ManagerId      Team
1      steve            1
2      bob      1      1
3      fred      1      1
4      barney      2      1
5      dino            2
6      bambam      5      2
7      pebbles      5      2
8      wilma      7      2

ASKER

stevetheski,

What does manager ID tell us ?

Thanks.

ASKER

is it possible to query all the employees under 0008 only using these 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

managerid is a foerign key to the UseridColumn in the same table
so when you insert a user if he has a manager or superior insert their userid or null if they are a top level e.g. Director.
usually i make my hierarchy have id's that are inverse of yours in case we have to add more lower level groups at a later date which is more common than adding managemt roles

ASKER

stevetheski,

Your a genius !!! then that means we only need two tables and we already have them just like how you've shown,

team_master = ManagerID,Team

operator_master = UserID, UserName

M I lost?

Thanks.

is it possible to query all the employees under 0008 only using these 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

SELECT team_master.TeamName,Parent.OperatorId as ImmediateBossId, child.*
FROM
operator_master AS child,
operator_master AS Parent,
team_master

WHERE
child.designationID = (Parent.designationID +1)
and Parent.operatorID = 0008
--and lookups.designationID = Child.designationID
and team_master.TeamId = cHILD.TeamId

ASKER

stevetheski,

Please completely ignore designation table. We're not going to use it anymore since there's a flaw and we already have what we need correct?

Could kindly assist on the sql query for listing the employees uder a specific operatorid. (is it possible?)

Thanks.

ASKER

stevetheski,

child.designationID = (Parent.designationID +1)
is the flaw since designation ID doesn't tell us the rank meaning who's boss of who.

Can we get the Boss of employee X using Team_master, since the designation table is going to be deleted on the database.

Team_master tells us which operatorID is the boss of who properly.

Thanks.

ASKER

stevetheski,

I didn't know you were a flinstone fan :-)

me too !!!

based on the 2 tables you have now
there is no link anywhere to determine who is a subordinate of who.
you will need to add a column to the operator_master which joins on the operatorID

i am going back to the original design of the operator_master table and claiming that designationID is the boss id so i dont have to build any more tables :)
with that as the case

you will note that
fk to userid to show boss
0001 abc1 1 5
0002 abc2 1 5
0003 abc3 1 5
0004 abc4 3 4
0005 abc5 2 5
0006 abc6 2 5
0007 abc7 3 4
0008 abc8 3
0009 abc9 2 5

005 / 003 / 004 have subordinates

select users.*
from operator_master manager, operator_master users
where manager.operatorid = users.designationID
and manager.operatorid = 5

if you just want the boss info

select Manager.*
from operator_master manager, operator_master users
where manager.operatorid = users.designationID
and users.operatorid = 3

Hey they're a Modern stone age family
any man that can walk around with a club and drag his wife around and we can laugh at it is my hero

ASKER

stevetheski,

I'm lost now doesn't teamID tell us that

we have 3 employees under teamID 1 (0001,0002,0003) and Team_master tells us that 0004 is the SupervisorID
we have 3 employees under teamID 2 (0005,0006,0009) and Team_master tells us that 0007 is the SupervisorID

and we have 2 employees on teamID 3 (0004,0007) and Team_master tells us that 008 is the supervisorID

where could the missing link be? kindly assist on the logic part i'm missing...

Yabadabadoo!!!

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

ASKER

We are querying all the employees under 0008

ASKER

The query below would give list all the employees under 0008 sadly it's not flexible enough..the query below only works for 3 levels deep, Manager, supervisor, employee.

which works perfectly, sadly it's limited to 3 levels which will not work for us...

select a.supervisorid as PMid,
c.teamName,
b.operatorID as SVid, b.operatorname as SVName,
d.operatorID, d.operatorname
from team_master a
left join operator_master b on b.teamid = a.teamid
left join team_master c on b.operatorid = c.supervisorid
left join operator_master d on c.teamid = d.teamid
where a.supervisorid='0008'

now i understand
a little hint for making things easier is to name your foreign keys the same as the parent key

so to make this easiest to read
and i am sure you will need the view for alot of things in nthe future

create view userteamsmanagers as
select a.TeamID, TeamName, SupervisorID,
operatorID, OperatorName
from team_Master a, Operator_Master b
where a.teamid = b.teamid

go
select users.*
from userteamsmanagers manager, userteamsmanagers users
where manager.operatorid = users.SupervisorID
and users.SupervisorID = 8

POST what you want as the result set from the query
especially if you wan a tree now

ASKER

the output should show as:

Output:
operatorID OperatorName
0001 abc1
0002 abc2
0003 abc3
0004 abc4
0005 abc5
0006 abc6
0007 abc7 <---------0008 should not show since it's the supervisor.
0009 abc9

when i run the query it only returned the columns...no records

ASKER

stevetheski,

Tried it for 4 and 2 levels deep.

director>manager>supervisor>employee (4 level)

supervisor > employee (2 level)

no results returned, only columns.

Thanks.

send me an emai; meATsteveski.com
then we can talk on AOL IM if you have

ASKER

Sad to say I dont have any IM accounts..

is there any issues using EE ?

no i just thought we could nail this down a little more quickly.

so do you one query that will return something like

for a supervisor show his immediate subordinates
then for each subordinate show their subordinates and so on till there are no more subordinates?

or do you just want a list of immediate subordinates for a specific supervisor?

ASKER

stevetheski,

Yes that is correct to show all his immediate subordinates and then for each subordinate show thier subordinate and so on till there are no more subordinates.

Thanks.

ok then we will need to use cursors and resursion. is this to be printed out from query analyzer or do you need to return a result set?

ASKER

stevetheski,

returning from the query analyzer since there will be more work done after query the list.

Thanks.

ALTER PROC GetSubs -- ad parms you wanna search for
AS
      BEGIN
            CREATE TABLE #TEMPoperator_master (
                  TeamID int,
                  TeamName varchar(50),
                  SupervisorID int,
                  operatorID int ,
                  OperatorName varchar(50) )
            EXEC ShowHierarchy 8;
            SELECT * FROM #TEMPoperator_master
            WHERE operatorID != 8
            DROP TABLE #TEMPoperator_master
      END

GO

ALTER PROCEDURE dbo.ShowHierarchy
(
      @Root INT
)
AS
BEGIN
      SET NOCOUNT ON
      DECLARE @operatorID INT, @OperatorName varchar(30)

      SET @OperatorName = (SELECT OperatorName FROM dbo.userteamsmanagers WHERE operatorID = @Root)
     PRINT REPLICATE('*', @@NESTLEVEL * 4) + @OperatorName --just for fun

      SET @operatorID = (SELECT MAX(operatorID) FROM dbo.userteamsmanagers WHERE SupervisorID = @Root)
      insert into #TEMPoperator_master select * from dbo.userteamsmanagers WHERE SupervisorID = @Root

      WHILE @operatorID != @Root
            BEGIN
                  EXEC dbo.ShowHierarchy @operatorID
                  SET @operatorID = (SELECT MAX(operatorID) FROM dbo.userteamsmanagers WHERE SupervisorID = @Root AND operatorID > @operatorID)
            END

END
GO

EXEC getSubs

oops replase MAX with MIN

ASKER

I've copy paste it to QA it eturns an error, kindly assist what can we do to troubleshoot.

Server: Msg 208, Level 16, State 6, Procedure ShowHierarchy, Line 65535
Invalid object name 'dbo.ShowHierarchy'.

same error for Getsub

ASKER

stevetheski,

PRINT REPLICATE('*', @@NESTLEVEL * 4) + @OperatorName --just for fun

What would this line produce? is it needed for our query ?

Thanks.

I think you need to add the supervisorid to the ops table
or you will just get data for 1 team

no if you run the query with text results you will see a tree

dont wory about theerrors they are because ShowHierarchy' calls itself and it isnt created when you create it.
same with the other proc. it is just being created before the proc it calls

juat play with the min and max values

generally the hierarchy goes like so
NAME ID BossId
superboss 1 null
SBSlave 2 1
SBSlave2 3 2
SBS2Slave 4 3

etc
then the query would be

SET @OperatorName = (SELECT OperatorName FROM dbo.userteamsmanagers WHERE operatorID = @Root)
PRINT REPLICATE('*', @@NESTLEVEL * 4) + @OperatorName --just for fun

SET @operatorID = (SELECT MIN(operatorID) FROM dbo.userteamsmanagers WHERE SupervisorID = @Root)
insert into #TEMPoperator_master select * from dbo.userteamsmanagers WHERE SupervisorID = @Root

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

Thats how i have hundreds of hierarchial tables and queries working
note that the boss id is in same table as child id if i need team info i could look that up in another table based on bosid or whatever

time to go play softball
Good luck and just work wth the query i sent.
if the table redesign is necessary do that i have spent lots of time working with these queries on partitioned views that contain hundreds of millions of rows

just wish i git the idea you wanted this from the first post

Steve

ASKER

stevetheski,

We can't alter the tables for operator_master and team_master since we dont have access rights. I made the designation table on the fly..which was a bad idea..

is there any workaround? (we're so near)

Thanks.

ASKER

stevetheski,

I see, finish creating both stored procedure; added the parameter @operatorid

create PROC GetSubs -- ad parms you wanna search for
@operatorid int
AS
BEGIN
CREATE TABLE #TEMPoperator_master (
TeamID int,
TeamName varchar(100),
SupervisorID int,
operatorID int ,
OperatorName varchar(200) )
EXEC ShowHierarchy @operatorid;
SELECT * FROM #TEMPoperator_master
WHERE operatorID != @operatorid
DROP TABLE #TEMPoperator_master
END

GO

Sadly after running it seem to return this error:

Server: Msg 208, Level 16, State 1, Procedure ShowHierarchy, Line 11
Invalid object name 'userteamsmanagers'.

(0 row(s) affected)

hope you could give guidance after you've played ball.

Thanks.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

stevetheski,

Thanks so much for your time and patience.