Left joins are not allowed in recursive CTE expressions, as are any outer joins. The issue is I need to check results the CTE has produced a recursion before. Some sample data is:WaterWorks:Id: Label:1 WaterWorks A5 WaterWorks B7 WaterWorks CPipelines:Id: ToId: FromId: ParentId (to calculate):2 3 1 1 (A) Connects from waterworks 1 to pipeline 33 8 3 1 (A) Connects from pipeline 3 to some other infrastructure 8 (house etc)4 8 5 5 (B) Connects from water works 5 to other infrastructure 8 (house etc)6 10 7 7 (C) Connects from water works 7 to other infrastructure 10 (house etc)9 11 7 7 (D) Connects from water works 7 to other pipeline 1111 12 9 7 (D) Connects from pipeline 9 to other pipeline 1212 13 11 7 (D) Connects from pipeline 11 to other pipeline 1313 7 12 7 (D) Connects from pipeline 12 to other water works 714 15 5 5 (E) Connects from water works 5 to pipeline 1515 1 14 5 (E) Connects from pipeline 14 to water works 116 20 1 1 (F) Connects from water works 1 to other infrastructure 2017 21 16 1 (F) Connects from pipeline 16 to other infrastructure 2118 22 16 1 (F) Connects from pipeline 16 to other infrastructure 2219 23 16 1 (F) Connects from pipeline 16 to other infrastructure 23
As a note all objects that are connectable, including pipelines themselves are of a supertype "WaterInfrastructure", and this is a table where all the unique ids are generated and stored.Group (A) is quite straightfoward, a simpline waterworks to pipeline to infrastructureGroup (B) is even more straighfoward, waterworks to infrastructureGroup (C) is the same as (B)Group (D) loops back to the same waterworks, and so the recursive query could run forever, and is why I need to check results a recursion beforeGroup (E) connects up two water works, and is also why I'd need to check previous resultsGroup (F) shows pipelines that split off from a main pipeline.Hope this makes sense. I have actually managed an iterative solution, but I thought a recursive CTE might be quicker and should be possible:--Gets the initial parent Ids for pipelines connected directly to a parentSELECT Pipe.Id, ParentId=Parent.Id, Pipe.FromId, Pipe.ToId, Cnt=0INTO #PFROM Pipelines Pipe LEFT JOIN WaterWorks ON Parent.Id=Pipe.FromId--Counts how far each pipeline is from a pipeline connected directly to parentDECLARE @Cnt intSET @Cnt=1--Populates pipelines that do not have a parent to pipeline that do and uses their parent id WHILE @@ROWCOUNT>0 AND @Cnt<20BEGIN UPDATE P2 SET ParentId=P1.ParentId, Cnt=@Cnt FROM #P P2 JOIN #P P1 ON ( P2.FromId=P1.Id --For pipelines that connect directly OR P2.FromId=P1.ToId --For pipelines that connect through some infrastructure ) WHERE P2.ParentId Is Null AND P1.Cnt=@Cnt-1 SET @Cnt=@cnt+1ENDSELECT * FROM #p