Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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.
;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

Open in new window

0
amoses
Asked:
amoses
  • 3
  • 2
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Hi. You can try putting that in its own CTE. Then just reference it in the recursive one.

;WITH c(name, CatalogID, parentcatalogid) AS (
SELECT 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
), cte (name, CatalogID, parentcatalogid) ...

Open in new window


Hope that helps!
0
 
knightEknightCommented:
Can you show us some sample data from each of the three tables?  thanks.
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry I was in a rush. In the second CTE, you would do an INNER JOIN:

from c
inner join cte on c.parentcatalogid=cte.[catalogid]


And so, you avoid the OUTER JOIN in recursive piece and life is good. :)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
amosesAuthor Commented:
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.

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)

Open in new window

0
 
amosesAuthor Commented:
I adapted mwvisa1's solution to fit my needs and posted it so it can help others.
0
 
Kevin CrossChief Technology OfficerCommented:
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
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
;

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now