• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

list all employees under a specific asst Director

Hi,

kindly assis in creating a query that will get 1 level higher.

This will list all employees under a specific Supervisor
design:
specific Supervisor > list ALL employees

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
where a.operatorid='0004'


This will list all employees under a specific Manager
design:
specific Manager > list ALL Supervisor > list ALL employee

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'

kindly assist how can we query all the employees for a specific Asst Director
design:
> ALL manager > ALL supervisor > ALL employees


--------------------------

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
EdwardPeter
Asked:
EdwardPeter
  • 4
  • 2
1 Solution
 
Melih SARICAIT ManagerCommented:
this questtion asked 3rd times be4.. and with different usernames ....


but this is ur solution


select drv.*,Operators.OperatorID from Operators inner join
      (select ManagerID,TeamID ,SuperVisorID from teams cross join
            (select OperatorID ManagerID from operators where TeamID is null) drv) drv
on drv.TeamID = Operators.TeamID and drv.supervisorID<>OperatorID

order by drv.TeamID,OperatorID
0
 
EdwardPeterAuthor Commented:
Non_zero,

How can we query using a specific Asst Director ID ?

Thanks.
0
 
EdwardPeterAuthor Commented:
Non_zero,

Not sure which to add in the where condition,

specific Asst Director > ALL manager > ALL supervisor > ALL employees

Where Operators.operatorid = 'Asst Directo ID'
or
Where SupervisorID ='Asst Directo ID'

Thanks.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
EdwardPeterAuthor Commented:
non_zero,

After running the kick ass query it returned 383,244 records.
the operator_master only has 1246 records.
the team_master only has 47 records.
There seems to be a loop whole.

by they way, my apology, it should read as:

specific Asst Director > ALL manager > ALL supervisor > ALL employees (only show employees)

Query all employees only under a specific Asst Director.
(include all the managers/supervisors from the search but dont add them in the result query list)


Sad to say really need it badly, been no luck on this, I'll repost and hope other experts will join.

Thanks so much for your time and patience.
0
 
Melih SARICAIT ManagerCommented:
i guess ur Db desing has a problem

to get all desired list .. u must add an other table to collect managers table

there is no connection between managers and other employess..
there must be a relation between managers and others...


Melih SARICA..

btw..

why u ave lots of rows from my query .. there is a crossjoin .. i guess that user ID 0008 is the manager cuz he is not in a team ..

and to show a manager i use him.. and cuz of the cross join ur row count is huge

0
 
EdwardPeterAuthor Commented:
non_zero,

There is a link, team_master table shows this.
i.e.

0004 is the supervisor for team1
0007 is the supervisor for team2

and 0008 is the supervisor for team3 (members are 0004 and 0007)

select 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'

The query above works perfectly in the scenario, sadly it's only limited to 3 levels, can we generate a 4 level deep?
Desgin:
Asst Direct > search all manager under him > search all supervisor under him > list all employees

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

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        
0

Featured Post

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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now