sql server programming: Hi,

I have the following tables.

DirTab
ID ParentID Directory
1 c:\
2 1 SubDir1
3 2 SubDir2


FileTab
ID File
1 Datei.txt


DirFileTab
DirID FileID
3 1

Now, I would like a query with output like this:

Directory File
c:\SubDir1\SubDir2 Datei.txt

How can I do that? How can I query the DirTab to receive the desired output?
Who has an idea or sample code?

Thanks for help

The trick is to create an UDF to recurse.

e.g.

create table DirTab(
ID int, ParentID int null, Directory sysname)

insert DirTab select 1,null,'c:'
union select 2, 1, 'SubDir1'
union select 3, 2, 'SubDir2'

create table FileTab(
ID int, [File] sysname)
insert FileTab select 1, 'Datei.txt'

create table DirFileTab(
DirID int, FileID int)
insert DirFileTab select 3, 1

go
create function dbo.GetDir(@dir int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000),@a int
set @a=@dir
set @s=''
while @a is not null
begin
select @s=Directory+'\'+@s
,@a=ParentID
from DirTab
where ID=@a
end
return @s
end
go
select dbo.GetDir(DirID) DirID, [File]
from DirFileTab join FileTab on DirFileTab.FileID=FileTab.ID
go
drop table DirFileTab,FileTab,DirTab
drop function dbo.GetDir


--
-oj


[quoted text, click to view]

Super, many thanks for your help!

*** Sent via Developersdex http://www.developersdex.com ***