Parent and all its children

[lameid]

I know I have seen an example of this but I just can't find it.

From a table that self join's how would I find a parent and all its children...

TableX

UID
PUID

Children are easy but grandchildren and further down is what elludes me.

 

[lameid]

Oops... I was thinking about SQL 2005 and it's Common Table Expressions.

In Access you would have to build a temp table with recordsets to get the recursion.

 

[Golom]

There's a way to do this with pure SQL but not with the table design you are using. Look at Joe Celko's Nested Sets example

 

[lameid]

Looks interesting... Too bad my previous experience with graph theory is none (may thanks to wikipedia).

All my backend's requiring this relationship are going to be in SQL 2005 anyway.

I was hoping to find a simpler SQL solution but I don't think there is one.

While I imagine this has it's place for things like least cost routing and near static Bill of Materials, I think most of what I need to do in the near future is more linear and dynamic. I am leaning towards not investing time into looking at this in depth unless you think there is a compelling reason to (i.e. my cursory review and conclusions are wrong).

 

[Golom]

Since I don't know what your requirements are, I really can't presume to know if the nested set thing makes any sense for you. It certainly represents s significant learning curve and, if you just want to solve this one problem, then cutting some code to do the recursion is probably easier.

 

[MajP]

This may not be of interest, but I use this idea to fill a tree view with a self referencing table. Oviously not a SQL solution. But if you can use recordsets then this recursive idea works well, just disregard all of the loading of the treeview.

 

[lameid]

I understood the learning curve piece almost instantly.

The assumption I am making is if I want to be able to add children and grandchildren or remove a thing from a hierarchy, it is no simple process to recreate the relevant nested set data. The best non-industry specific similar thing I can think of is grafting and pruning Active Directory forests (although with greater frequency than you might expect for that situation). Add to this the need to be able to recreate the hierarchy as it existed at a point of time to perform some functions.

I hope that is specific enough without being too generic.

I guess another way to clarify it for me is what type of situation would make a nested set superior?

 

[Golom]

Nested sets find application in Bill-of-Materials applications and in representing tree-structures in a relational system.

Their big down-side is, if you have a requirement to restructure the heirarchy on a frequent basis, then the processing can really eat your lunch.

I have looked into nested sets and really liked the simplicity of drilling down to essentially unlimited depths but stayed away on a couple of occasions because rebuilding the heirarchy was very complex and prohibitively expensive.