Retrieval of Heirarchical records in child to Parent/root Fashion

Question

Retrieval of Heirarchical records in child to Parent/root Fashion

Tarun Upadhyaya-195062

SSC Journeyman

Points: 97
More actions
January 11, 2005 at 11:39 pm

#66432

Hi,
I have following table structure,
CREATE TABLE [ModulePermission] (
[ModulePermissionID] [int] IDENTITY (1, 1) NOT NULL ,
[ModuleId] [int] NULL ,
[Label] [varchar] (20) NULL ,
[ParentPermissionID] [int] NULL ,
CONSTRAINT [PK_ModulePermission] PRIMARY KEY CLUSTERED
(
  [ModulePermissionID]
&nbsp ON [PRIMARY]
) ON [PRIMARY]
GO

See the sample data and notice that ModulePermissionID and ParentPermissionId are having parent-child relationship.
1 1 Rough Purchase  0
2 1 Purchase Requisiton  1
3 1 New  2
4 1 Edit  2
5 1 From PO  2
6 1 From Process  2
7 1 Rough Receipts  0
8 1 From supplier  7
9 1 New  8
10 1 Edit  8
11 1 Delete  8
Suppose i have a permissoin Edit (See above ID 4), i wish to retrive all the parent IDs as well.
e.g
1 Rough Purchase 0
2 Purchase Req.   1
4 Edit                 2
I am able to retrive the heirarchy from Parent to child (If I know Parent ID), but unable to do vice versa.
Any suggestions, scripts, links, or articles would help.
I appreciate if you read the post carefully first and then plan for any reply.
Regards

Viewing 10 posts - 1 through 9 (of 9 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Read it as carefully as possible - no pics in my browser, so I have no idea what the table or data looks like. (it's preferrable to post DDL and data as plain ascii in the mail for easy copy & paste)

Anyway, suggestion for dealing with hierachies. Have you read what BOL has to say about it? (the part about 'Expanding Hierarchies' is usually a good place to start)

/Kenneth

Tarun Upadhyaya-195062 SSC Journeyman Points: 97 More actions · Answer 2

Hi,

I have removed the pics from the post and updated it with dml statements and sample data.

Please proceed as needed.

Regards

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Great - looks better now.

Have you read what BOL has on the subject?

/Kenneth

Tarun Upadhyaya-195062 SSC Journeyman Points: 97 More actions · Answer 4

Hi,

Yes i had already seen it , but it is talking about the situation when u have parent id and u wish to traverse all its child.

in my situation i have the child id and i need to know all its parents. in other words from bottom to top.

Regards

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

If the child has knowledge about it's parent, then you can walk that way in much the same fashion. If the child does not know which it's parent is, then you can't do it. Sometimes, depending on how encumbring walking hierarchy trees becomes, some also store the ultimate parent (ie the mother or root) along wiht each child, that way you'd always know where to start.

However, I don't quite follow in your example how the parent/childs are related..

/Kenneth

Tarun Upadhyaya-195062 SSC Journeyman Points: 97 More actions · Answer 6

hi,

Thanks

Even i was thinking the same, but somehow now able to write a performance oriented script.

I tired to write a recursive UDF that make uf Cursor, but it failed.

Can u suggest me some way like using cursors or UDFs, with some sample script.

Regards .

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Any hierarchy represented by child + parentid is 'by design' not performance oriented in a relational language like SQL. SQL is simply not suitable for hierarchies of any kind. It can be done, but many times it's ugly and not performing very well.

What you can do, and what many does, is 'cheat'. One example is like I mentioned, to keep track of the root id with every child, another is to keep track of the entire branch child-parent, which level the child is on etc..

I don't have any ready examples or scripts, but there is a lot to find on google

BTW, I still don't quite understand how the parent/child relations works with the below example...

1 Rough Purchase 0

2 Purchase Req. 1

4 Edit 2

/Kenneth

Tarun Upadhyaya-195062 SSC Journeyman Points: 97 More actions · Answer 8

Yes , i agree.

1 Rough Purchase 0

2 Purchase Req. 1

4 Edit 2

For your Reference consider the Label Edit with ID 4 and its parent id is 2

that means Purchase Req. which has parent id 1 which is Rough Purchase, since it is root, its parent id is 0.

You can see it from child to parent like 4->2->1->0

or Edit->Purchase Req->Rough Purchase.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

Okay, so that means that each child knows which is it's parent.

1 1 Rough Purchase 0

2 1 Purchase Requisiton 1

3 1 New 2

4 1 Edit 2

5 1 From PO 2

6 1 From Process 2

7 1 Rough Receipts 0

8 1 From supplier 7

9 1 New 8

10 1 Edit 8

11 1 Delete 8

Which then would bring the above to a like tree

0 (unknown mother - root)

/

1 Rough Purchase 7 Rough Receipts

/

2 Purchase Requisiton 8 From supplier

/ | \ \ / |

3 New 4 Edit 5 From PO 6 From Process 9 New 10 Edit 11 Delete

Since each child knows it's parent, you can easily see which it parent is. You then have to go fetch that in order to see it's parent etc... and walk the tree upwards in that fashion.

It's not very elegant, or efficient - you're forced to iterate through each node row-by-row, so if this is a large table you shouldn't expect too great performance. But, it's the only way I can see that it can be done, given the current model.

/Kenneth