InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:35:28 GMT20http://www.sqlservercentral.com/Forums/Topic1422726-391-1.aspxYep, you can have multiple queries in your cte. It can make things a lot easier to read and understand, but sometimes I think it may have an affect on performance.Fri, 22 Feb 2013 13:11:13 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1422726-391-1.aspxThank you very much for your help. I was under the impression that after using a CTE, you had to SELECT from it. I didn't realise you could create multiple CTEs and then combine them as you have.Fri, 22 Feb 2013 02:35:24 GMTsku370870http://www.sqlservercentral.com/Forums/Topic1422726-391-1.aspxHiYou're very close to getting the result you want, you just need to combine the results of your drill up with a drill down from FTID, rather than drilling down from the TopBaseIDSomething like:[code="sql"];WITH rDrillUp AS ( SELECT FTIDBase, FTIDCalc, 0 datelevel FROM #fieldrels WHERE FTIDCalc = 7 UNION ALL SELECT fr.FTIDBase, fr.FTIDCalc, datelevel - 1 -- Down Count FROM rDrillUp du INNER JOIN #fieldrels fr ON du.FTIDBase = fr.FTIDCalc ) ,rDrillDown AS ( SELECT FTIDBase, FTIDCalc, 0 datelevel FROM #fieldrels WHERE FTIDCalc = 7 UNION ALL SELECT fr.FTIDBase, fr.FTIDCalc, datelevel + 1 -- Up Count FROM rDrillDown du INNER JOIN #fieldrels fr ON fr.FTIDBase = du.FTIDCalc ) ,combined AS ( SELECT FTIDBase, FTIDCalc, DENSE_RANK() OVER (ORDER BY DateLevel) + 1 DateLevel FROM ( SELECT * FROM rDrillUp UNION SELECT * FROM rDrillDown ) a )SELECT FTIDBase, FTIDBase FTIDCalc, 1FROM combinedWHERE DateLevel = 2UNION ALLSELECT FTIDBase, FTIDCalc, DateLevelFROM combined[/code]Thu, 21 Feb 2013 12:24:04 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1422726-391-1.aspxSorry for the subject, can't think of a better way of describing what I am trying to do. This is a sort of follow-up to a question I asked recently about sorting a recursion - which I have done okay (and thanks again to those that helped me on that.) If I start with the same data ...[code="sql"]DECLARE @DateLevel int = 0, @DateLevelUp int, @FTID int = 8, @TopBaseID intCREATE TABLE #FieldRels ( FTIDBase int, FTIDCalc int, Interval int )INSERT INTO #FieldRels(FTIDBase, FTIDCalc)SELECT 6, 7 UNION ALLSELECT 7, 8 UNION ALLSELECT 7, 12 UNION ALLSELECT 8, 13 UNION ALLSELECT 6, 5 UNION ALLSELECT 8, 9 UNION ALLSELECT 9, 16/*this data shows a relationship between the first and second columns.So:7 (second column, first row) relates to 6 (first column, first row)8 relates to 712 relates to 713 relates to 85 relates to 69 relates to 816 relates to 9I'm interested in the relationships 'above' and 'below' Item 8 (which is why @FTID is hard coded to 8)*/IF EXISTS(SELECT 0 FROM #FieldRels WHERE FTIDCalc = @FTID) --the field passed in has a parent - work up the chain to the parent BEGIN ;WITH rCTE(FTIDBase, FTIDCalc, DateLevel) AS ( SELECT FTIDBase, FTIDCalc, 1 AS DateLevel FROM #FieldRels WHERE FTIDCalc = @FTID UNION ALL SELECT e.FTIDBase, e.FTIDCalc, DateLevel + 1 FROM #FieldRels e INNER JOIN rCTE c ON e.FTIDCalc = c.FTIDBase ) SELECT TOP 1 @TopBASEID = FTIDBase FROM rCTE ORDER BY DateLevel DESC END ELSE BEGIN SET @TopBASEID = @FTID END--@TopBaseID is the ID of the 'highest' field above Item 8 that it relates to--now work down from there to get all the relationships above and below Item 8 ;WITH rCTE(FTIDCalc, FTIDBase, DateLevel, SortKey) AS ( SELECT TOP 1 FTIDBase, FTIDBase, 1 AS DateLevel, CAST('\'+CAST(FTIDBase AS VARCHAR(10)) AS VARCHAR(4000)) FROM #FieldRels WHERE FTIDBase = @TopBASEID UNION ALL SELECT e.FTIDCalc, e.FTIDBase, DateLevel + 1, CAST(d.SortKey + '\'+CAST(e.FTIDCalc AS VARCHAR(10)) AS VARCHAR(4000)) FROM #FieldRels e INNER JOIN rCTE d ON e.FTIDBase = d.FTIDCalc ) --this is the recordset that populates the Gridview below the Stage Dates on each stage (shown when a user clicks on the Field Description so they --can see what date relationships the field is in. SELECT r.FTIDBase, r.FTIDCalc, DateLevel, CASE WHEN @FTID = r.FTIDCalc THEN 'true' ELSE 'false' END AS [ThisOne] FROM rCTE r ORDER BY SortKeyDROP TABLE #FieldRels[/code]The code above returns:FTIDBase-----FTIDCalc-----DateLevel6------------------6---------------16------------------5---------------26------------------7---------------27------------------12---------------37------------------8---------------38------------------13---------------48------------------9---------------49------------------16---------------5But, I don't want some of these rows:6------------------6---------------16------------------5---------------2 /// I don't want this one because it is above 8 but does not lead down to Item 86------------------7---------------27------------------12---------------3 /// I don't want this one because it is level with Item 87------------------8---------------38------------------13---------------4 // I do want this as it is below Item 88------------------9---------------4 // I do want this as it is below Item 89------------------16---------------5 // I do want this as it is below Item 8Using recursion to go 'up' the data to the highest related item and then using recursion to go 'down' the data to the lowest related data gives me extraneous rows. Having gone 'up' the data to Item 6, on the way down I am not interested in the fact that Item 5 relates to Item 6 because Item 5 does not lead me back down to Item 8.However, once I reach Item 8 I do want to see every item below it.Thu, 21 Feb 2013 11:17:59 GMTsku370870