Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

query 4 level higher

Hi,

kindly assist in creating a query that will get 4 level higher, we need to only list all employees.

This will list all employees under a specific Supervisor   (2 level high)
design:
specific Supervisor > list ALL employees  

select a.operatorid, a.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  (3 level high)
design:
specific Manager > search ALL Supervisor > list ALL employee

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'

kindly assist how can we query all the employees for a specific Asst Director   (4 level high)
design:
specific Asst Director> search ALL manager > search ALL supervisor > list 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
  • 9
  • 2
1 Solution
 
Melih SARICAIT ManagerCommented:
there is no relation between level 4 and level 2..

u cant get the desired output ..

U ask this Question many many  times....

0
 
EdwardPeterAuthor Commented:
non_zero,

Wanted to get different opinion from other experts.

Got it, missing link from level 4 to level 2.

What would the new table look like, the columns needed for the new table?

Thanks.
0
 
EdwardPeterAuthor Commented:
non_zero,

By the way, we will need to go as deep as 6 level deep.

Thanks.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
EdwardPeterAuthor Commented:
non_zero,

specific Vice President > Director > Asst Directo > Manager > Supervisor >employees

Thanks.
0
 
EdwardPeterAuthor Commented:
non_zero,

Oh my, are you refering to the sample data? My apology, the sample data only goes upto level 3.

Thanks.

Here's a sample data of level 4:

0010 Asst Director >
0008 Manager>
0004 and 0007 supervisors >
employees..

team_master
TeamID    TeamName   SupervisorID (Foreign key to operatorID)
1                   team1          0004
2                   team2          0007
3                   team3          0008
4                   team4          0010

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              4            
0009                abc9              2          



0
 
EdwardPeterAuthor Commented:
Forgot to add

operator_master
operatorID OperatorName TeamID
0010                abc10              
0
 
Melih SARICAIT ManagerCommented:
First ..
Once u ask a Question .. different Experts ll give answers .. Dont Worry About it... U dont need to Ask the same question Again and Again ..
If u cant get any Solution From EE .. it means there is not an Answer for ur Question ...

Second..

U must Create an Relation Table..

its can be something Like this..

ManagerID OperatorID

in both columns u must enter Operators ID's

this table ll show who is the Manager of Who ?

it ll be multi level as multi as u can imagine... :p

for Example

MID  OID
0008 0004
0008 0007
0004 0001
0004 0002
0004 0003
0007 0006
0006 0005
0005 0009

from here we ll understand that 0008 is the top
0008 has 2 operators.. and these 2 operators r also managers of others
and 0007 is the manager of 006
0006 is the manager of 0005
0005 is the manager of 0009

u see4 levels

if add table 0009 as a manager then u ll get 5 levels


u can solve ur problem like this



0
 
EdwardPeterAuthor Commented:
Melih SARICA ,

Oh my, I think I may be wrong here, my apology if I miss understood.

Doesn't the two table generate something like that ?

operator_master
operatorID OperatorName TeamID  MID           <------MID with OID
0001                abc1              1       0004
0002                abc2              1       0004    
0003                abc3              1       0004    
0004                abc4              3       0008  
0005                abc5              2       0007    
0006                abc6              2       0007    
0007                abc7              3       0008    
0008                abc8              4       0010      
0009                abc9              2       0007
0010                abc10            4

I'm missing something on the table you've shown me, if you could kinly adive what it is..sorry for being slow on this, been 24 hours still no sleep yet.

Thanks.

0
 
EdwardPeterAuthor Commented:
Two table meaning,

Operator_master and team_master
0
 
EdwardPeterAuthor Commented:
Melih SARICA ,

Is the table order the one I'm missing ? The Asst Director should be on top ?
meaning row number 1 is highest seated position then 2 is the second highest position ? and ...etc

Please kindly advice time is against me.

Thanks.

>>
ID  MID  OID          <-------added ID for clearification.
1   0008 0004
2   0008 0007
3   0004 0001
4   0004 0002
5   0004 0003
6   0007 0006
7   0006 0005
8   0005 0009
<<
0
 
EdwardPeterAuthor Commented:
Melih SARICA ,

I got it, I'll repost using the new structure you've shown me and hope there would be some experts would join in.

thanks so much for your time and patience
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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