query employees

Hi,

Kindly assist to how to query all the employees under operatorid 0008. (organization chart)
0008 is the project manager, who are the employees below him and their respective team supervisor.

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

output columns:
operatorID operatorName supervisorName supervisorID


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
EdwardPeterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
puranik_pCommented:
Thats perfectly right query.
Just add filter to it.

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 c.operatorid = '0008'
0
 
lluthienCommented:
let me get this straight

you 've got three levels:
project manager,
team supervisor,
team member

right?
0
 
EdwardPeterAuthor Commented:
lluthien,

That is correct, it can go 2 levels deeper.

i.e.

president
director
project manager
team supervisor
team mamber

Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lluthienCommented:
i think we are missing a link between the project manager and the supervisors..

you want this:
projectmanager [PM]
PM's supervisors [SV]
SV's operators,

right?

i think you still have a table that says PM is in charge of some SV.
0
 
EdwardPeterAuthor Commented:
Hope this helps.

there's a link where we can catch i'm not sure how but I believe it can be done.

two tables
operator_master
operatorID OperatorName TeamID
0001                abc1              1<------------------team1 under SV 0004
0002                abc2              1<------------------team1 under SV 0004
0003                abc3              1<------------------team1 under SV 0004
0004                abc4              3<--------------------team3 under PM 0008
0005                abc5              2<------------------team2 under SV 0007
0006                abc6              2<------------------team2 under SV 0007
0007                abc7              3<--------------------team3 under PM 0008
0008                abc8              
0009                abc9              2<------------------team2 under SV 0007

team_master
TeamID TeamName SupervisorID (this is same as operatorID)
1                   team1         0004 <------------SV
2                   team2         0007 <-------------SV
3                   team3         0008        <---------PM
0
 
lluthienCommented:
i'm pretty sure this link is not in these two tables.

unless..
there is only one PM in the database
0
 
EdwardPeterAuthor Commented:
lluthien,

I'm not sure, but can recursive query help in this case?

Thanks.
0
 
EdwardPeterAuthor Commented:
lluthien,

There are 4 PM, 2 director and 1 Persident. (it can grow) like 2 president.

Thanks.

0
 
lluthienCommented:
depending on the rest of the database contents,
it might, but..

you still don't have the information saying that PM
is in indirectly in charge of team 1 and 2 (via the two SV's)

what you could achieve with recursion is finding out who is in charge of a team
and by checking if the SV is member of the team,
you can see if the SV is PM or not.

but still, you miss the link SV <> PM
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
lluthienCommented:
if you have more than 1 PM, you have the problem that your recursion stops at the PM.

you need a table saying

Supervisor          SV's Projectmanager
0004                  0008
0007                  0008

OR
a table saying
Team            SV's Projectmanager
1                  0008
2                  0008

otherwise the connection cannot be made
0
 
EdwardPeterAuthor Commented:
lluthien

in that case, i'll accept that as an answer and report to get other opinion from o ther experts.

Thanks.
0
 
lluthienCommented:
you have no other tables to work with?
0
 
EdwardPeterAuthor Commented:
lluthien,

presently none.

Thanks.
0
 
EdwardPeterAuthor Commented:
lluthien,

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 b.supervisorid='0009'

I've tried to run this query, can you kindly assist how can we search each operatorid that cameup on the list if they have any memeber below them?

i.e.
operatorid operatorname teamName SupervisorID supervisorname
0001              abc              team1        0009                defg
0002              abc              team2        0009                tuvw

search 0001 and 0002 if they have they show up in team_master if so, run the uqery again.

this would show us the link from PM to SV to member

kindly assist on the sql query

Thanks.

0
 
lluthienCommented:
ah..
i seem to notice something;

team 3 consists only of supervisors, right?
0
 
EdwardPeterAuthor Commented:
lluthien,

yupyup !!! hope we get it !!!

Thanks.
0
 
lluthienCommented:
mhmm..
had connection problems..

try this:

select a.operatorid as PMid, a.operatorname as PMName,
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.id
left join team_master c on b.operatorid = c.supervisorid
left join operator_master d on c.id = d.teamid
where a.supervisorid='0009'

0
 
EdwardPeterAuthor Commented:
lluthien,

There are two colimns ID that would give us error, which ID should we place? Team or operator or supervisor?

Thanks.
0
 
lluthienCommented:
select a.operatorid as PMid, a.operatorname as PMName,
c.teamName,
b.operatorID as SVid, b.operatorname as SVName,
d.operatorID, d.operatorname


all the ID's have aliasses.. are you sure your getting an error?
0
 
EdwardPeterAuthor Commented:
lluthien,

We almost had it !!! only one tweak left, for supervisor ID it's not showing the memebers.

how can we tweak the query to start at supervisor ID instead of PM ID

I'll use two different query to query two different levels..I hope there could be a single query though...

kinda like:

operatorID 0001  
search for members in team_master....
found
0002
0003
now search again for members in team_master...
found
0004
0005
0006
0007


Thanks.


0
 
lluthienCommented:
could you post the result of that query and what exactly you want fixed?
0
 
EdwardPeterAuthor Commented:
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='1002'

This one rocks. is queries PM ID and all the memebers below.

kindly assist how can we create another one for SV ID and all the memebers below.
0
 
lluthienCommented:
what it is supposed to show is:

PMID, teamName, SVID, SVNAME, operatorID, operatorNAME

0008, 1, 0004, 'abc4', 0001, 'abc1'
0008, 1, 0004, 'abc4', 0002, 'abc2'
0008, 1, 0004, 'abc4', 0003, 'abc3'
0008, 3, 0004, 'abc4', 0004, 'abc4'
0008, 2, 0007, 'abc7', 0005, 'abc5'
0008, 2, 0007, 'abc7', 0006, 'abc6'
0008, 3, 0007, 'abc7', 0007, 'abc7'

doesn't it?
0
 
EdwardPeterAuthor Commented:
lluthien,

The query you've posted works like a charm for PM ID.

Columns needed for SVID are:

TeamName, SVID, SVNAME, operatorID, operatorName    

(we can leave PMID since we are going to enter the SVID this time)

if possible can we generate only one query to return all the memebers may it be SVID or PMID.

Thanks.


0
 
EdwardPeterAuthor Commented:
lluthien,

If we place PM ID on your query it generated NULL values for the other columns.

Thanks.
0
 
lluthienCommented:
right, this query:
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='1002'

gives normal results,
while
where a.supervisorid='1008'

in the end gives a lot of null's?

did i get this right?
0
 
EdwardPeterAuthor Commented:
lluthien

Yes that is correct, when we are short 1 level, it tends to give us null values.

1008 is a SVID

1002 is a PMID

Thanks.
0
 
EdwardPeterAuthor Commented:
lluthien,

You're the BEST!!!

I've found out what's happening, It's okay now, there was a problem with the team grouping that's why it produce null.

It's very clear now, we need a table to identify how many level does a particular position has under him.

Table called organization_chart
Position            organizationlevel
Director                      4
Asst direcor                3
Manager                     2
Supervisor                  1
Memeber                    0

is this correct ?  if so kindly assist how can we query the memeber for a particular position.

i.e.
list all the members under Asst director.

Thanks.
0
 
EdwardPeterAuthor Commented:
lluthien,

kindly refer to the link below for new table.

http://experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21416010.html

Thanks.
0
 
lluthienCommented:
glad to help out,

i'll check out that one later, bit busy at the moment

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.