You are trying to accomplish the following:
ITMS 8.7.x
--Option 1:
--Simplified version
DECLARE @FolderGuid uniqueidentifier
SET @FolderGuid = '448cd45e-9e1e-47c7-b33d-a261911c3ee9' --Replace with Root Folder GUID
SELECT vc.[Name]
, vc.[OS Name]
, vi.[Name] as [Filter Name]
, ifo.[Name] as [Folder]
FROM FolderBaseFolder fo
JOIN ItemFolder vfo on vfo.ParentFolderGuid = fo.FolderGuid
JOIN [Collection] col on col.[Guid] = vfo.[ItemGuid]
JOIN Item vi on vi.[Guid] = col.[Guid]
JOIN Item ifo on ifo.[Guid] = vfo.ParentFolderGuid
JOIN CollectionMembership cm on cm.CollectionGuid = col.[Guid]
JOIN Inv_AeX_AC_Identification vc on vc.[_ResourceGuid] = cm.[ResourceGuid]
WHERE
fo.ParentFolderGuid = @FolderGuid
ORDER BY 1,4
--Option 2:
--Using a custom function "fnGetFolderPathNames"
--Helper Function (to show whole path to folder)
drop function dbo.fnGetFolderPathNames
go
create function dbo.fnGetFolderPathNames (@folderGuid uniqueidentifier, @maxParent uniqueidentifier, @reverse bit)
returns nvarchar(4000)
as
begin
declare @n nvarchar(1000); select top 1 @n = [Name] from Item where [Guid] = @folderGuid
declare @p uniqueidentifier; select top 1 @p = fo.ParentFolderGuid from ItemFolder fo where fo.ItemGuid = @folderGuid
if @p is null or @p = 0x0 or @folderGuid = @maxParent
return @n
if @reverse = 1
return @n + ' / ' + dbo.fnGetFolderPathNames(@p, @maxParent, 1)
return dbo.fnGetFolderPathNames(@p, @maxParent, 0) + ' / ' + @n
end
go
--Main Query
select vc.[Name]
, vc.[OS Name]
, vi.[Name] as [Filter Name]
, dbo.fnGetFolderPathNames(vfo.ParentFolderGuid, @FolderGuid, 1) as [Folder]
from FolderBaseFolder fo
join ItemFolder vfo on vfo.ParentFolderGuid = fo.FolderGuid
join [Collection] col on col.[Guid] = vfo.[ItemGuid]
join Item vi on vi.[Guid] = col.[Guid]
join CollectionMembership cm on cm.CollectionGuid = col.[Guid]
join Inv_AeX_AC_Identification vc on vc.[_ResourceGuid] = cm.[ResourceGuid]
where
fo.ParentFolderGuid = @FolderGuid