SQL Parent Child relationship

I am working with a table that hosts folders and contents (articles). I need to do a total counts of the contents under the main folders,
I have a root folder called MainRoot, and there are four sub-folders (sub1-4), some of the sub folders could also have some sub-folders.

I have two tables that holds the Folder Names and the Contents. Each folder row has a parent folder name under the folders name, so I can easily pull all the desired folders by matching the parent folder to be the MainRoot folder.

The content table has an associated folder id that can also be joined to the parent folder (one folder higher), but the challenge is when having more than 1 sub folders under the root folder.

I am looking for some direction or guidance on how tackle this.
thanks

Google the Nested Sets model for trees and hierarchies :slightly_smiling:

1 Like

thank you! I got this article that talks about nested sets in great detail.
http://www.codeproject.com/Articles/4155/Improve-hierarchy-performance-using-nested-sets

Keep your original parent/child structure (also known as an "Adjacency List") because it's easy for humans to understand and maintain rather than doing a FULL conversion. Then, read the following article for how to do a "parallel" conversion on-demand faster than the ol' push-stack method.
http://www.sqlservercentral.com/articles/Hierarchy/94040/