I have a CTE and need to use a LEFT join in the recursive member. I get the error stating that an outer join in not possible in the recursive member. Is there any work around to this limitation?
Below is the simplified query I'd like to run.
;WITH cte (name, CatalogID, parentcatalogid) AS
( select CI.[Name], CI.CatalogID, NULL as ParentCatalogID
from [CatalogInfo] CI
select convert(varchar(max),cte.name+' | '+ (COALESCE(C.[Name], CLc.[Name]))) , C.catalogid, C.parentcatalogid
from [Catalog] as C
LEFT join CatalogLC CLc ON CLc.CatalogID = C.CatalogID AND CLc.LCID = 1033
inner join cte on C.parentcatalogid=cte.[catalogid]
SELECT cte.CatalogID, cte.name