Saving a tree structure in a database.
In case this is relevant, I am using MS SQL.
I have my site organized in a tree structure. Let's just say each row contains the following information.
ID | Name | ParentID | Permissions
Where the Permissions colomn contains permissions for the associated node in the tree.
Now, when I load a node, I want to lead the node's parents, and so forth up to the root. Because not all permissions will be set in the permissions column for every node, and any permissions that aren't set need to be fetched from the parent node and so forth.
So, I figure I need to select the necessary node & all the parents. Is there a way to do this in one query? What would be the best way to do it?