Solved

Is there any work around to use a Left Join in a SQL Server CTE in the recursive member

Posted on 2011-09-20
6
234 Views
Last Modified: 2012-05-12
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
Comment
Question by:amoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36568078
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 36568101
Can you show us some sample data from each of the three tables?  thanks.
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 36568131
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:amoses
ID: 36954324
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
 

Author Closing Comment

by:amoses
ID: 36954343
I adapted mwvisa1's solution to fit my needs and posted it so it can help others.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36956434
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question