Dear All,
I need to create a query to list all the subfolders within a folder.
I have a database table that lists the usual properties of each of the folder.
I have another database table that has two columns
1. Parent folder
2. Child folder
But this table maintains the parent child relationship only to one level.
For example if i have a folder X that has a subfolder Y and Z.
And Y has subfolders A and B.
and B has subfolder C and D
and C has subfolder E and F
The database table will look like
parentfolder child folder
X Y
X Z
Y A
Y B
B C
B D
C E
C F
I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.
I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.
The APPLY operator will do what you need.
Check out:
http://msdn2.microsoft.com/en-us/library/ms175156.aspx
For a description and an example that pretty much is like your needs.
|||In sql server 2005 you can use CTE..
Code Snippet
Create Table #folder (
[parentfolder] Varchar(100) ,
[childfolder] Varchar(100)
);
Insert Into #folder Values('X','Y');
Insert Into #folder Values('X','Z');
Insert Into #folder Values('Y','A');
Insert Into #folder Values('Y','B');
Insert Into #folder Values('B','C');
Insert Into #folder Values('B','D');
Insert Into #folder Values('C','E');
Insert Into #folder Values('C','F');
;With CTE([parentfolder],[childfolder],[Level],[Paths]) as
(
Select [parentfolder],[childfolder], 1 Level, Cast(Parentfolder + '\' + childfolder as varchar) Paths From #folder Where parentfolder = 'X'
UNION ALL
Select data.[parentfolder],data.[childfolder], Level + 1,Cast(Paths + '\' + data.[childfolder] as varchar)From #folder Data Join CTE On Data.ParentFolder = CTE.childfolder
)
Select * from CTE Order By Paths
|||Nicely done Mani!