imitating nested "FOREACH" loop in SQL Query

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!