amoses
asked on
Is there any work around to use a Left Join in a SQL Server CTE in the recursive member
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.
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
union all
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
from cte
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you show us some sample data from each of the three tables? thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the delay in getting back.
I created a variable with the CTE and inserted it into a table variable. Thanks mwvisa1 for pointing me in the right direction.
I created a variable with the CTE and inserted it into a table variable. Thanks mwvisa1 for pointing me in the right direction.
DECLARE @LeafNodes TABLE (CatalogID INT);
DECLARE @vSQL nvarchar(MAX);
SET @vSQL = '
WITH cte (name, CatalogID, parentcatalogid) AS
( select CI.[Name], CI.CatalogID, NULL as ParentCatalogID
from [CatalogInfo] CI
union all
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 from cte'
INSERT INTO @LeafNodes (CatalogID)
exec (@vSQL)
ASKER
I adapted mwvisa1's solution to fit my needs and posted it so it can help others.
Glad to help. By the way, you probably do not need the dynamic SQL (see attached). You still show the LEFT JOIN, but the point I am making below is you can have the INSERT as part of the final selection.
Best regards and happy coding,
Kevin
Best regards and happy coding,
Kevin
DECLARE @LeafNodes TABLE (CatalogID INT);
;WITH cte (name, CatalogID, parentcatalogid) AS
(
select CI.[Name], CI.CatalogID, NULL as ParentCatalogID
from [CatalogInfo] CI
union all
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]
)
INSERT INTO @LeafNodes (CatalogID)
SELECT cte.CatalogID from cte
;