Multi-parent heirarchy query

Question

Post reply

Multi-parent heirarchy query

james.mcallester

SSC Journeyman

Points: 90
More actions
July 14, 2010 at 5:07 pm

#223391

Hi All
I'm using SQL 2005 and have a heirarchy structure in which a child could have multiple parents. E.g. a user who could report to different managers (say depending on the type of task). Managers are also users and may have multiple parents also.
For example, in terms of being able to "see" a user, manager1 might see userA, userB and userC. Manager2 might see userD, userC and userE.
Thus userC has 2 parents.
I have built this with a 2-table structure as follows:
User (ID int, Name nvarchar(100))
Manager (ID int, MgrUserID int, UserID int)
So using my example above, userC would have 2 entries in the Manager table one for Manager1 and one for Manager2.
What I need to do is, given a manager's ID, return all children for that manager, including all levels. So if I provide the ID of a Senior manager, I will get a list of IDs including all the managers he can see and all of their users, etc.
I can't seem to find a clear example of this - lots of single table ones but not quite what I need.
I'd appreciate any help on this
Thanks, James

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

1. Could you provide more sample data with which to test?

2. Can a manager of usera also report to usera for a different project?

3. Since this also involves project management, what is the structure for the project data?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

I would have a number of concerns about what you're trying to do. If managers can have multiple underlings and multiple managers themselves, you're going to end up with a muck. To me it sounds like maybe what you need to do is create a structure based on a hierarchy of groups and simply assign the people to the groups.

However, without structure definitions, sample data and desired output it's very difficult to help you with what you're trying to do, either in terms of advice or practical code samples. Check the link in my signature below to get an idea of what you should provide.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Joe Celko (7/15/2010)
Talking about multiple parents in a Hierarchy makes no seine by definition.

Full on agree with that.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

CirquedeSQLeil (7/15/2010)
Joe Celko (7/15/2010)
Talking about multiple parents in a Hierarchy makes no seine by definition.
Full on agree with that.

Maybe for a hierarchy but have you ever seen a BOM? 😉 Multiple assemblies can use the same partnumber for a screw, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

Joe Celko (7/16/2010)
The nodes (parts) are in one table and the tree structure (blueprint) is in another. The (lft, rgt) pairs are the primary in a BOM tree so I can have multiple references to the same part. I do one of these things in TREES & HIERARCHIES.

Z'actly 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

james.mcallester SSC Journeyman Points: 90 More actions · Answer 6

Thanks to all who replied to this. Only had a chance to come back now to provide a final, closing post.

The problem was solved by the follow CTE:

DECLARE @ManagerID int; -- The manager who wants to list his "reports"

WITH DirectReports(SupervisorID, SalesRepID) AS

(

SELECT SupervisorID, SalesRepID

FROM SalesSupervisors

WHERE SupervisorID = @ManagerID

UNION ALL

SELECT e.SupervisorID, e.SalesRepID

FROM SalesSupervisors e

INNER JOIN DirectReports d

ON e.SupervisorID = d.SalesRepID

)

SELECT d.SalesRepID, s.Name

FROM DirectReports d

LEFT JOIN SalesReps s

ON d.SalesrepID = s.SalesRepID;

Thanks again, James

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

james.mcallester (9/12/2010)
Thanks to all who replied to this. Only had a chance to come back now to provide a final, closing post.
The problem was solved by the follow CTE:
DECLARE @ManagerID int; -- The manager who wants to list his "reports"
WITH DirectReports(SupervisorID, SalesRepID) AS
(
SELECT SupervisorID, SalesRepID
FROM SalesSupervisors
WHERE SupervisorID = @ManagerID
UNION ALL
SELECT e.SupervisorID, e.SalesRepID
FROM SalesSupervisors e
INNER JOIN DirectReports d
ON e.SupervisorID = d.SalesRepID
)
SELECT d.SalesRepID, s.Name
FROM DirectReports d
LEFT JOIN SalesReps s
ON d.SalesrepID = s.SalesRepID;
Thanks again, James

Thanks for the feedback, James. Folks read a bit more into your requirements than what were actually there.

If I recall correctly, Celko calls that bit of recursive code "Lasagne" code because it "returns a layer (level) at a time". 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)