How do I do a multiple level Nested Loop Join?

Question

I'm sure this must be a common coding question, but I don't even know what to call it.

For any given Person, I want to find their team - now there may be teams inside of teams, or maybe there are not - it's not predictable.

So I can just do nested loops which is fine, but I'm wondering if there is smarter fancier way of doing it? I'll just end up iterating over the same dataset (about 10,000 records, representing maybe 7 levels of hierarchy) over and over again, which isn't the most efficient.

MS SQL Server 2012? Python 3.10?

PersonID	PersonName	Lev3	Lev2	Lev1	ManagerID
4703	John	Department A	Group D	Division A	5763
4367	Janet	Department B	Group E	Division B	4744
7033	Bob	Department C	Group D	Division A	5763
5763	Sarah	None	Group D	Division A	5224
4744	Phil	None	Group E	Division B	5224
5224	Petra	None	None	None	None

Let's say I want to find Petra's team - ID 5224 - it should be everybody, since she is top of the food chain. But Phil? ID 4744 - that's only Janet. Compared to Sarah - ID 5763 who has both John and Bob in her team.

This SQL code will identify only the immediate team - not the teams within teams.

SELECT
   M.[Employee Name], S.[Employee Name]
FROM [Staff] M
JOIN [Staff] S
ON M.PositionID = S.ManagerPosition
WHERE 
    M.PositionID > 0
ORDER BY 
    m.[Employee Name]

How could I so a nested loop with a nested loop, within a nested loop - upto 7 levels deep?

Answer 1

You will need to use a recursive CTE. Read this and see if you can apply it to your case: https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/