Self-join Query? 2

[georgesOne]

Hi All:

I finally figured out that I can get a Supervisor / Employee relationship through a self join like:

SELECT tblStaff.EmployeeID, tblStaff.Manager, tblStaff.[Last Name] & ", " & tblStaff.[First Name] AS Employee
FROM tblStaff INNER JOIN tblStaff AS Supervisors ON tblStaff.Manager = Supervisors.EmployeeID
ORDER BY Supervisors.[Last Name] desc, tblStaff.[Last Name];

Which gives me

ID Manager Employee
3 Vinet, M. Garcia, Mike
7 Vinet, M. Mendes, Joe
12 Vinet, M. Myers, Klaus
27 Myers, K. Antonio, Bernadette
29 Myers, K. Gonzales, Frederic

Obviously, Myers, K(laus) is a manager managed by Vinet, M.

How can I get the result:

ID Level1 Level2 Level3 Level4.......
3 Vinet, M. Garcia, M.
7 Vinet, M. Mendes, J.
12 Vinet, M. Myers, K. Antonio, B.
12 Vinet, M. Myers, K. Gonzales, F.

and so on, similar what you get in a treeview.
What I want is that each Level can have access to all the lower level records, e.g. Vinet should be able to see Antonio, B.'s record.

Any suggestion is welcome.

Georges.

 

[r937]

it's easy

you know how you wrote your query with 2 "copies" of the tblStaff table? one for the manager, and one for the 1st level down?

all you have to do is repeat the join for each additional level going down

the table aliases can get hairy, so what i do is use aliases like "down1" and "down2"

this might help -- Categories and Subcategories



r937.com | rudy.ca

 

[georgesOne]

Easy?...depends...don't know.
But you were very fast (thanks a lot), and I think I understand where to go.
Otherwise I will post again.
Thanks also for the article.

Georges

 

[georgesOne]

Well, I'm tired...basically I followed the article sqllessons.com

I always get a syntax error:

SELECT root.[Last Name] AS root_name, down1.name AS down1_name, down2.name AS down2_name, down3.name AS down3_name, down4.name AS down4_name
FROM tblStaff AS root
LEFT Outer JOIN tblStaff AS down1 ON down1.Manager = root.EmployeeID
LEFT OUTER JOIN tblStaff as down2 on down2.Manager = down1.EmployeeID
LEFT OUTER JOIN tblStaff as down3 on down3.Manager = down2.EmployeeID
LEFT OUTER JOIN tblStaff as down4 on down4.Manager = down3.EmployeeID
WHERE (root.Manager) Is Null))
ORDER BY root_name, down1_name, down2_name, down3_name, down4_name;

Any idea?

 

[PHV]

SELECT root.[Last Name] AS root_name, down1.name AS down1_name, down2.name AS down2_name, down3.name AS down3_name, down4.name AS down4_name
FROM [!]((([/!]tblStaff AS root
LEFT JOIN tblStaff AS down1 ON down1.Manager = root.EmployeeID[!])[/!]
LEFT JOIN tblStaff as down2 on down2.Manager = down1.EmployeeID[!])[/!]
LEFT JOIN tblStaff as down3 on down3.Manager = down2.EmployeeID[!])[/!]
LEFT JOIN tblStaff as down4 on down4.Manager = down3.EmployeeID
WHERE [!]root.Manager Is Null[/!]
ORDER BY root_name, down1_name, down2_name, down3_name, down4_name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[georgesOne]

Hi PH,

it helped (as usual when you respond!)! A few modifications (the query could not understand the order arguments)... so finally it looks like:

SELECT root.[Last Name] & ", " & root.[First Name] AS root_name, IIf(IsNull(down1.[Last Name]), "",down1.[Last Name] & ", " & down1.[First Name]) AS down1_name, IIf(IsNull(down2.[Last Name]), "",down2.[Last Name] & ", " & down2.[First Name]) AS down2_name, IIf(IsNull(down3.[Last Name]), "",down3.[Last Name] & ", " & down3.[First Name]) AS down3_name, IIf(IsNull(down4.[Last Name]), "",down4.[Last Name] & ", " & down4.[First Name]) AS down4_name
FROM (((tblStaff AS root LEFT JOIN tblStaff AS down1 ON down1.Manager = root.EmployeeID) LEFT JOIN tblStaff AS down2 ON down2.Manager = down1.EmployeeID) LEFT JOIN tblStaff AS down3 ON down3.Manager = down2.EmployeeID) LEFT JOIN tblStaff AS down4 ON down4.Manager = down3.EmployeeID
WHERE root.Manager Is Null
ORDER BY root.[Last Name], down1.[Last Name], down2.[Last Name], down3.[Last Name], down4.[Last Name];

Thanks a lot.
But then, the field number can vary, if the hierarchy becomes deeper. Is there a way to create exactly the number of fields as required to display the hierarchy? (I understand that I can add another field 'down5_name', etc. so the question is more out of interest than out of need).

Kind regards, Georges.

 

[r937]

adding another column and another left outer join is the easiest

i would start out by coding it four or five levels deeper than the expected maximum

;-)

r937.com | rudy.ca

 

[georgesOne]

I am really excited!
This forum is great!
I work in a very different area and there is not such a support, and especially, willingness to support even if the question may be considered dumb by you experts.

Thanks a lot to all experts, Georges.