May 25, 2012 04:54 PM|mattcase|LINK
I have a follow-up to the same question. I have it working great for the original post. However, I am trying to modify it so that I can use it in a different way and I am having trouble.
What I want to do is take the current folder that a user is in and select all parent folders up to the highest point then list them as quick links to go back to any given parent folder.
It doesn't seem to be working regardless of how I adjust it.
What are your thoughts?
The following statement works in the it will do what I need it to, except that it stops working when I get down to the last folder in the chain. In addition, it shows both the current folder that the user is in and also its immediate sub-folder (child folder).
WITH FolderCount AS (Select FolderID FROM Table1 WHERE ParentFolderID IS NULL AND FolderID = '" & FolderID & "' UNION ALL Select e.FolderID FROM Table1 e INNER JOIN FolderCount ecte ON ecte.FolderID = e.ParentFolderID) SELECT FolderID FROM FolderCount