SELECT DISTINCT
ParentApp.DisplayName ParentAppName,
ParentAppF.Name ParentAppFolder,
ParentApp.CreatedBy ParentAppCreatedBy,
ParentAppDT.DisplayName ParentAppDTName,
ChildApp.DisplayName ChildAppDisplayName,
ChildAppF.Name ChildAppFolder,
ChildAppDT.DisplayName ChildAppDTName
FROM
fn_ListApplicationCIs(1033) ParentApp
LEFT JOIN vFolderMembers ParentAppFM on ParentAppFM.InstanceKey = ParentApp.ModelName
LEFT JOIN vSMS_Folders ParentAppF on parentAppF.ContainerNodeID = ParentAppFM.ContainerNodeID
LEFT JOIN fn_ListDeploymentTypeCIs(1033) ParentAppDT on ParentAppDT.AppModelName = ParentApp.ModelName
LEFT JOIN vSMS_AppRelation_Flat R on R.FromApplicationCIID = ParentApp.CI_ID
LEFT JOIN fn_ListApplicationCIs(1033) ChildApp on ChildApp.CI_ID = R.ToApplicationCIID And ChildApp.IsLatest = 1
LEFT JOIN vFolderMembers ChildAppFM on ChildAppFM.InstanceKey = ChildApp.ModelName
LEFT JOIN vSMS_Folders ChildAppF on ChildAppF.ContainerNodeID = ChildAppFM.ContainerNodeID
LEFT JOIN fn_ListDeploymentTypeCIs(1033) ChildAppDT on ChildAppDT.AppModelName = ChildApp.ModelName
WHERE
ParentApp.IsLatest = 1
AND ParentAppDT.IsLatest = 1
AND ChildApp.IsLatest = 1
AND ChildAppDT.IsLatest = 1
ORDER BY 1