Start Free Trial

asked on

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

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'

let me get this straight

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

right?

ASKER

lluthien,

That is correct, it can go 2 levels deeper.

i.e.

president
director
project manager
team supervisor
team mamber

Thanks.

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.

ASKER

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

i'm pretty sure this link is not in these two tables.

unless..
there is only one PM in the database

ASKER

lluthien,

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

Thanks.

ASKER

lluthien,

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

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

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

ASKER

lluthien

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

Thanks.

you have no other tables to work with?

ASKER

lluthien,

presently none.

Thanks.

ASKER

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.

ah..
i seem to notice something;

team 3 consists only of supervisors, right?

ASKER

lluthien,

yupyup !!! hope we get it !!!

Thanks.

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'

ASKER

lluthien,

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

Thanks.

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?

ASKER

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.

could you post the result of that query and what exactly you want fixed?

ASKER

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.

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?

ASKER

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.

ASKER

lluthien,

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

Thanks.

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?

ASKER

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.

ASKER

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.

ASKER

lluthien,

kindly refer to the link below for new table.

https://www.experts-exchange.com/questions/21416010/recursive-query.html

Thanks.

glad to help out,

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