like best of photo from http://www.marcduerst.com/

oke, so have the procedures or function get full path when SELECT Top 20 left(gs_MediaObject.Title,50) as title, [MediaObjectId], 'mediaobjects/' + [DirectoryName] + '/' + [OptimizedFilename] as thumb, [gs_MediaObject].[DateAdded]as DateAdded FROM [gs_MediaObject] join [gs_Album] on [FKAlbumId] = [AlbumId] ORDER BY gs_MediaObject.DateAdded DESC

Oki....I think


create function GetFullPath(@AlbumId int )
returns nvarchar(1000)
as
begin
declare @Stop bit, @DirectoryNameFull nvarchar(255), @Parent int, @temp nvarchar(255),@FullDirectoryName nvarchar(255)
set @DirectoryNameFull = ''
if (not exists(select * from gs_Album where AlbumId = @AlbumId))
set @FullDirectoryName = ''
else
begin
set @Stop = 0
while (@Stop = 0)
begin
select @Parent = (select AlbumParentID from gs_Album where AlbumId = @AlbumId)
if (@Parent=1)
begin
set @temp =(select DirectoryName from gs_Album where AlbumId = @AlbumId)
set @DirectoryNameFull = '/' + RTRIM(@temp)+ '/' + LTRIM(@DirectoryNameFull)
set @Stop = 1
end
else
begin
set @temp =(select DirectoryName from gs_Album where AlbumId = @AlbumId)
set @DirectoryNameFull = RTRIM(@temp)+ '/' + LTRIM(@DirectoryNameFull)
set @AlbumId = (select AlbumParentID from gs_Album where AlbumId = @AlbumId)
end
end
end
return @DirectoryNameFull
end