No doubt this has already been answered and probably detailed many ways so I just need to know the right key words to find the post or a link if anyone has it.
I'm trying to write a query that will show all levels of Supervisor to Employee relations ships where the relationships can go several levels deep; employee reports to employee 10 who reports to employee 100 and so on. Unlike the referencing setup where 1 table has all the employees and a column in it links the employee to their supervisor I'm working with 2 tables; 1 for the employees called PERSON and another that has 1 row for each Employee to Supervisor relationship.
There is an existing query (see below) that will do this but its got a max of 4 levels so if there are more than 4 levels to this hierarchy it doesn't show every one. So this has been fine to use because we've not exceeded that many levels and I know I could append another copy of each table as the existing query does to cover another level but I'm looking for something that will work no matter how many levels they're are, something recursive I guess. I'd be great to if the query could also indicate what level each employee is for a specif department starting with @mySupervisorCode as shown below.
If the supervisor is 'IT0001' then the query should show every person that 'IT0001' is the Supervisor for directly or indirectly and go down until the last employee whom is not the supervisor of anyone is listed. If John reports to Sam and Sam reports to john and john reports to Larry and Larry reports to Max and Max reports to IT0001 than within this the employee John would be level 5 while the employee IT0001 would be at level 1.
I've also posted the DDL for these 2 tables but I have removed all superfluous columns (address, Name, ect) that aren't necessary to answer the question. The PERSON table is very wide and most of it has nothing to do with this relationship thing so I removed all the unnecessary columns.
SELECT PL.hMy hSupervisor,
FROM PERSON PL Left Outer Join PERSONXREF X ON PL.hMy = X.hSupervisor
Left Outer Join PERSON P ON X.hPERSON = P.hMy
Left Outer Join PERSONXREF X1 ON X1.hSupervisor = P.hMy
Left Outer Join PERSON P1 ON X1.hPERSON = P1.hMy
Left Outer Join PERSONXREF X2 ON X2.hSupervisor = P1.hMy
Left Outer Join PERSON P2 ON X2.hPERSON = P2.hMy
Left Outer Join PERSONXREF X3 ON X3.hSupervisor = P2.hMy
Left Outer Join PERSON P3 ON X3.hPERSON = P3.hMy
WHERE PL.sCode = @mySupervisorCode
CREATE TABLE dbo.PERSON ( hMy NUMERIC(18, 0) NOT NULL IDENTITY(1, 1),
sCode CHAR(8) NOT NULL,
iType NUMERIC (18, 0) NULL
) ON [PRIMARY]
CREATE TABLE dbo.PERSONXREF ( hMy NUMERIC(18, 0) NOT NULL IDENTITY(1, 1),
hPerson NUMERIC(18, 0) NOT NULL,
hManager NUMERIC(18, 0) NULL
) ON [PRIMARY]
- This topic was modified 1 month ago by YSLGuru.
Just say No to Facebook!