I'm using SQL Server 2019. I have a complex organisational structure where an employee may have multiple managers and a manager may have multiple employees. Plus there are many layers of management involved. As such, we've got a parent/child table to hold all the relationships rather than a hierarchy. From here though, I can use a CTE to traverse the list to see which downstream employees are linked to a particular manager or which upstream managers are linked to an employee.
What I'm struggling with is to find an easy way to identify common employees for managers that may be several, and different, levels up the organisation.
For example (note that the hierarchy path doesn't exist in the database but is generated by a CTE for each example manager):
Manager #461
Parent | Child -> Example hierarchy output path
461 | 400 -> /461/400/
461 | 401 -> /461/401/
400 | 336 -> /461/400/336/
400 | 337 -> /461/400/337/
400 | 338 -> /461/400/338/
401 | 336 -> /461/401/336/
401 | 337 -> /461/401/337/
401 | 338 -> /461/401/338/
337 | 500 -> /461/400/337/500
-> /461/401/337/500
338 | 500 -> /461/400/338/500
-> /461/401/338/500
Manager #462
Parent | Child -> Example hierarchy output path
462 | 337 -> /462/337/
462 | 338 -> /462/338/
337 | 500 -> /462/337/500/
338 | 500 -> /462/338/500/
What I would like to do is ask what are the employees linked to both 461 and 462. The answers would be 337 and 338 but what I want to exclude is that 500 is also a common employee. I'd like to ultimately come up with a table like:
Manager1 | Path1 | Manager2 | Path2
461 | /461/400/337/ | 462 | /462/337/
461 | /461/401/337/ | 462 | /462/337/
461 | /461/400/338/ | 462 | /462/338/
461 | /461/401/338/ | 462 | /462/338/
I've tried making a table by joining on the last node ID (SELECT * FROM output1 JOIN output2 on RIGHT(output1.path,4) = RIGHT(output2.path,4)) but this also includes the 500 paths. What I'd like to do is somehow exclude any descendant nodes once an ancestor node is matched.
Current output (bad):
Manager1 | Path1 | Manager2 | Path2
461 | /461/400/337/ | 462 | /462/337/
461 | /461/401/337/ | 462 | /462/337/
461 | /461/400/338/ | 462 | /462/338/
461 | /461/401/338/ | 462 | /462/338/
461 | /461/400/337/500/ | 462 | /462/337/500/
461 | /461/401/337/500/ | 462 | /462/337/500/
461 | /461/400/338/500/ | 462 | /462/338/500/
461 | /461/401/338/500/ | 462 | /462/338/500/
Is there a way to stop the join or delete records from the output table if an ancestor already exists in the output table?
Just to make it more complicated, this is a simple example. There are many layers to the structure and there might be another common descendant that's branched off to the side many levels up or down so just grouping by path length and selecting the shortest path doesn't work. That's why I don't want to look at path length or hierarchy levels as the filter but instead check if a common ancestor exists and if so, stop joining further descendants or remove them from the output.
Thanks