InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 14:44:11 GMT20http://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]mister.magoo (12/2/2012)[/b][hr]I think there is a problem with duplicates...although this test data may not be valid.[/quote]I found another issue with the recursive CTE solution, so I have removed it for the time being.Sun, 02 Dec 2012 19:51:18 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxAs is becoming a habit, I would like to offer up my Identity Hack - rCTE replacement... It performs quite nicely and doesn't suffer from the dupes problem I have seen in some others - I think...[code="sql"]---- Create a temp table to hold the results---- include an identity column to keep track of the recursion---- include a computed column to keep track of whether we are---- looking for InvoiceId or BookingId, which flips every other ---- loop, so first we look for BookingId, then InvoiceIdIF OBJECT_ID('tempdb..#idhack') IS NULLBEGIN CREATE TABLE #idhack( Depth INT IDENTITY(1,1) NOT NULL, InvoiceId INT NOT NULL, BookingId INT NOT NULL, InvOrBook AS (Depth & 1) PERSISTED -- Flips every other row. ); CREATE UNIQUE CLUSTERED INDEX ix_bid ON #idhack(BookingId,InvoiceId); CREATE UNIQUE INDEX ix_invid ON #idhack(InvoiceId,BookingId) include (InvOrBook);ENDDECLARE @InvoiceID INTEGER = 3;-- Enable identity insert so we can insert our own values and have duplicates.SET IDENTITY_INSERT #idhack ON;-- put the first invoice into the resultsINSERT #idhack(Depth, InvoiceId, BookingId)SELECT 1, ids.InvoiceId, ids.BookingIdFROM #IDS AS idsWHERE ids.InvoiceId = @InvoiceID;-- recurse down looking for more invoices / bookings-- use SCOPE_IDENTITY to tell us which level we are on.WHILE @@ROWCOUNT>0 INSERT #idhack(Depth, InvoiceId, BookingId) SELECT idh.Depth+1, ids.InvoiceId, ids.BookingId FROM #IDS AS ids JOIN #idhack idh ON (InvOrBook = 1 AND ids.BookingId = idh.BookingId) OR (InvOrBook = 0 AND ids.InvoiceId = idh.InvoiceId) WHERE idh.Depth = SCOPE_IDENTITY() AND NOT EXISTS ( SELECT 1 FROM #idhack idh2 WHERE idh2.InvoiceId = ids.InvoiceId AND idh2.BookingId = ids.BookingId );-- turn off identity insertSET IDENTITY_INSERT #idhack OFF; -- and select the results SELECT InvoiceId, BookingIdFROM #idhack;[/code]Sun, 02 Dec 2012 18:50:52 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]SQL Kiwi (12/1/2012)[/b][hr]The set-based iteration (WHILE loop) solution may well be best here, but the following is a recursive CTE solution that appears to work correctly:[/quote]Hi Paul, I think there is a problem with duplicates...although this test data may not be valid.Try it with this sample data:[code="sql"]INSERT INTO #IDs( InvoiceId, BookingId) VALUES (1,1), (1,2), (2,1), (2,2);[/code]You get this:[img]http://www.sqlservercentral.com/Forums/Attachment12785.aspx[/img]Sun, 02 Dec 2012 17:51:00 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]SQL Kiwi (12/1/2012)[/b][hr]The set-based iteration (WHILE loop) solution may well be best here, but the following is a recursive CTE solution that appears to work correctly:[/quote]All "seeks", too! Nicely done.Sun, 02 Dec 2012 07:23:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx-- removed --Sat, 01 Dec 2012 21:53:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]Evil Kraig F (11/28/2012)[/b][hr]This is NOT pretty, but it IS functional.[code="sql"]--am trying to write a query against a table of invoices, returning all relevant rows for a particular InvoiceId. Heres some sample data:--Create temp table to hold the dummy dataif object_id('tempdb..#IDs') is not nulldrop table #IDsif object_id('tempdb..#TempStore') is not nulldrop table #TempStorecreate table #IDs (InvoiceId int not null,BookingId int not null) on [PRIMARY]goalter table #IDs add constraint PK_IDs primary key clustered ( InvoiceId ,BookingId ) with ( STATISTICS_NORECOMPUTE = off ,IGNORE_DUP_KEY = off ,ALLOW_ROW_LOCKS = on ,ALLOW_PAGE_LOCKS = on ) on [PRIMARY]goinsert #IDs ( InvoiceId, BookingId) select * from (values (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)) data(InvoiceId,BookingId)select * from #IDsDECLARE @InvoiceID INT, @Rowcount INTSELECT @InvoiceID = 1, @Rowcount = 1CREATE TABLE #TempStore (InvoiceID INT, BookingID INT)INSERT INTO #TempStoreSELECT InvoiceID, BookingIDFROM #IDsWHERE InvoiceID = @InvoiceID-- Set this here, might as well not hit the loop if no records to work from.SELECT @Rowcount = @@ROWCOUNTWHILE @Rowcount <> 0BEGIN INSERT INTO #TempStore SELECT ids2.InvoiceID, ids2.BookingID FROM #IDs JOIN (SELECT DISTINCT BookingID FROM #TempStore) AS drv ON #IDs.BookingID = drv.BookingID JOIN #IDs AS ids2 ON #IDs.InvoiceID = ids2.InvoiceID WHERE #IDs.InvoiceID NOT IN (SELECT DISTINCT InvoiceID FROM #TempStore) SET @Rowcount = @@ROWCOUNTENDSELECT * FROM #TempStore[/code][/quote]It might not be pretty but it appears to be the fastest... and I can't beat it. Nicely done, Craig!Sat, 01 Dec 2012 19:45:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxLooking at the data that you've included, Craig, I'm thinking that maybe I didn't have it working at all when it comes to "disconnected" data like you did with the 2,12 entry. Thanks for the test. Back to the drawing board. :blush:Sat, 01 Dec 2012 13:18:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxHeh... dammit. I had it working and made a final tweek to the code. Now I have to go back and figure out what the heck I changed. Sorry for the mistake. I'll fix it (I think) and will repost it. I took down my original post because it was wrong.Sat, 01 Dec 2012 12:57:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx*wipes away a tear from his eye* Damn, Jeff... that's... that's just beautiful. *sniff*I couldn't for the life of me figure out how to get out of the IN (SELECT InvoiceID From rCTE) situation. Only one problem.It doesn't recurse past the first series of booking IDs. Check this out:[code="sql"]if object_id('tempdb..#IDs') is not nulldrop table #IDscreate table #IDs (InvoiceId int not null,BookingId int not null) on [PRIMARY]goalter table #IDs add constraint PK_IDs primary key clustered ( InvoiceId ,BookingId ) with ( STATISTICS_NORECOMPUTE = off ,IGNORE_DUP_KEY = off ,ALLOW_ROW_LOCKS = on ,ALLOW_PAGE_LOCKS = on ) on [PRIMARY]goINSERT #IDs VALUES (1,9)INSERT #IDs VALUES (1,10)INSERT #IDs VALUES (1,11)INSERT #IDs VALUES (2,11)INSERT #IDs VALUES (2,12)INSERT #IDs VALUES (3,12)INSERT #IDs VALUES (3,13)INSERT #IDs VALUES (4,14)INSERT #IDs VALUES (5,14)GO--insert #IDs ( InvoiceId, BookingId) --select * from (values (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)) data(InvoiceId,BookingId)select * from #IDs--=================================================================================-- Solve the problem using some "standard" "Traveling Salesman" or "Airline-- Flight Schedule" code. I believe you'll be absolutely amazed as the-- performance of this code.-- This code could easily be turned into an iTVF to be used in CROSS APPLY as-- if it were a parameterized view.--=================================================================================--===== Start a timer because I don't trust SET STATISTICS any more (1). -- Ref (1): http://www.sqlservercentral.com/articles/T-SQL/91724/DECLARE @StartTime DATETIME; SELECT @StartTime = GETDATE();--===== Declare a variable for the original InvoiceID that we want to find and -- stack the others against. This is like identifying the "Start City" -- in a "Traveling Salesman" problem.DECLARE @StartInvoiceID INT; SELECT @StartInvoiceID = 1; --<<< Change this to do other tests. --<<< Any number from 1 to 300,000 will do.--===== Solve the problem as if it were a "Traveling Salesman" problem. The key -- to this is to leave "breadcrumbs" as to where we've been by using a -- "Materialized Hierarchical Path" (the "BeenThere" column).WITHcteBuildPath AS( --=== Find the "top" ID as we would in any hierarchy or net. SELECT a.InvoiceId, a.BookingID, BeenThere = CAST(QUOTENAME(a.InvoiceID,'"') AS VARCHAR(8000)) FROM #IDs AS a --as in "anchor" WHERE InvoiceID = @StartInvoiceID UNION ALL --==== "Loop" through all of the possibilities with some "stop code" to prevent -- the inevitable cycles that will appear because it's not a "DAG". SELECT b.InvoiceID, b.BookingID, BeenThere = r.BeenThere + CAST(QUOTENAME(b.InvoiceID,'"') AS VARCHAR(8000)) FROM #IDs b --as in "base table" JOIN cteBuildPath r --as in "recursive part" ON r.BookingID = b.BookingId WHERE r.BeenThere NOT LIKE '%'+QUOTENAME(b.InvoiceId,'"')+'%') SELECT * FROM cteBuildPath------=== Find the full data for the InvoiceID's that we've touched at least once.-- -- Note that the WHERE IN acts as if it were a DISTINCT so no need to explicity-- -- use the word DISTINCT. -- SELECT o.*-- FROM #IDs o --as in "original" -- WHERE o.InvoiceID IN (SELECT InvoiceId FROM cteBuildPath)-- ORDER BY o.InvoiceID, o.BookingID--;--===== Let's see how long that took. PRINT 'Total Duration = ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114)GO[/code]The result set stops at InvoiceID 2, and doesn't chain to 3 after picking up the BookingIDs for Invoice 2. Sorry for the horrible INSERT statement but I've only got 2k5 at the house.Sat, 01 Dec 2012 12:48:47 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxPost withdrawn. I made a mistake in the code somewhere. I'll post it as a new post if I can figure out what the heck I did wrong and fix it.Sat, 01 Dec 2012 12:27:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]Alan.B (11/28/2012)[/b][hr][quote][b]Evil Kraig F (11/28/2012)[/b][hr]This is NOT pretty, but it IS functional.[/quote]I am trying to understand why went with a loop vs a set-based approach. I posted a more set based method earlier that gets the same results notably faster. I am not trying to be confrontational, I'm just trying to understand your approach or what I did wrong.[/quote]If the problem requires that, no matter which InvoiceID you pick, all associated rows for all associated InvoiceIDs will be returned, then it actually can't be done in what most folks consider to be a "set based" manner. There needs to be some form of recursion either as a WHILE loop or as an rCTE (Recursive CTE).I will say that the WHILE loop and the rCTE method can more closely come to "set based" in that sets of rows can be returned for each iteration but it can't be done in a single itermation (what most folks think of as "set based") without giving up on the trait of "unknown depth" as some of the set based solutions on this thread have.Sat, 01 Dec 2012 09:43:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxOK, sorry. I only read the first few posts and made some faulty assumptions. After considering the last few comments I can see that it really is more complicated than it first appeared. Mea culpa. Fri, 30 Nov 2012 20:51:18 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]Steven Willis (11/30/2012)[/b][hr]No loops, no cursors, no cte[/quote]You've answered the data, not the problem, with this. You cannot recurse in both directions, you cannot deal with more than 2 layers of heirarchy. You need to be able to traverse bi-directionally across bookingIDs and InvoiceIDs as well as deal with multiple invoice-booking-invoice-booking-invoice chains.Fri, 30 Nov 2012 20:28:08 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]capnhector (11/30/2012)[/b]so far an extension of my adjacency list by searching up and down (not the fastest thanks Mickey T. by the way) and [b]dwains loop solution [/b]are the only ones that seem to work fully on the set of data we have.[/quote]Erm, that was mine, as well. Dwain's is basically a variation. I still need a chance to check performance between the two.Fri, 30 Nov 2012 20:25:41 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]Steven Willis (11/30/2012)[/b][hr]No loops, no cursors, no cte[code="sql"]IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTableCREATE TABLE #TempTable ( InvoiceId INT NOT NULL ,BookingId INT NOT NULL ,PRIMARY KEY (InvoiceId,BookingId) )INSERT #TempTable ( InvoiceId ,BookingId ) SELECT * FROM ( VALUES ( 1, 9), ( 1, 10), ( 1, 11), ( 2, 11), ( 3, 11), ( 3, 12), ( 3, 13), ( 4, 14), ( 5, 14) ) data (InvoiceId,BookingId) SELECT DISTINCT t2.InvoiceId ,t2.BookingIdFROM ( SELECT MAX(BookingId) AS BookingId FROM #TempTable GROUP BY BookingId ) AS Sub1INNER JOIN #TempTable AS t1 ON Sub1.BookingId = t1.BookingIdINNER JOIN #TempTable AS t2 ON t1.BookingId = t2.BookingId + 1[/code][/quote]The problem with your code is that if the value (3,14) is present it does not get picked up and neither does (4,14) or (5,14)[code="sql"]IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTableCREATE TABLE #TempTable ( InvoiceId INT NOT NULL ,BookingId INT NOT NULL ,PRIMARY KEY (InvoiceId,BookingId) )INSERT #TempTable ( InvoiceId ,BookingId ) SELECT * FROM ( VALUES ( 1, 9), ( 1, 10), ( 1, 11), ( 2, 11), ( 3, 11), ( 3, 12), ( 3, 13), (3,14), ( 4, 14), ( 5, 14) ) data (InvoiceId,BookingId)[/code]so far an extension of my adjacency list by searching up and down (not the fastest thanks Mickey T. by the way) and dwains loop solution are the only ones that seem to work fully on the set of data we have.Fri, 30 Nov 2012 15:42:56 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxNo loops, no cursors, no cte[code="sql"]IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTableCREATE TABLE #TempTable ( InvoiceId INT NOT NULL ,BookingId INT NOT NULL ,PRIMARY KEY (InvoiceId,BookingId) )INSERT #TempTable ( InvoiceId ,BookingId ) SELECT * FROM ( VALUES ( 1, 9), ( 1, 10), ( 1, 11), ( 2, 11), ( 3, 11), ( 3, 12), ( 3, 13), ( 4, 14), ( 5, 14) ) data (InvoiceId,BookingId) SELECT DISTINCT t2.InvoiceId ,t2.BookingIdFROM ( SELECT MAX(BookingId) AS BookingId FROM #TempTable GROUP BY BookingId ) AS Sub1INNER JOIN #TempTable AS t1 ON Sub1.BookingId = t1.BookingIdINNER JOIN #TempTable AS t2 ON t1.BookingId = t2.BookingId + 1[/code]Fri, 30 Nov 2012 15:32:21 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxPost deleted - SSC hung and posted this one to the wrong thread.Thu, 29 Nov 2012 23:27:31 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxHi Phil - allow me to propose something for you.[code="sql"]DECLARE @RowCount INTDECLARE @IDs TABLE (InvoiceId int not null ,BookingId int not null)INSERT INTO @IDsSELECT InvoiceID, BookingIDFROM #IDsWHERE InvoiceID = 1SET @RowCount = @@ROWCOUNTWHILE @RowCount <> 0BEGIN INSERT INTO @IDs SELECT a.InvoiceID, a.BookingID FROM #IDs a INNER JOIN @IDs b ON a.BookingID = b.BookingID UNION ALL SELECT a.InvoiceID, a.BookingID FROM #IDs a INNER JOIN @IDs b ON a.InvoiceID = b.InvoiceID EXCEPT SELECT a.InvoiceID, a.BookingID FROM @IDs a SET @RowCount = @@ROWCOUNT ENDSELECT * FROM @IDs[/code]I think this resolves the original and the alternate data setup (from Kraig?) properly. As it turns out, I recently solved an problem quite similar to this and this solution has its basis in that.Not to disappoint you Alan (as this uses a loop), it is at least a set-based loop and in the similar problem I solved any attempt at a recursive solution did not perform nearly as well. I have not done that testing here so I can't say for sure whether it will be faster or not.The other advantage to this approach is that it eliminates feedback loops which will completely disintegrate any effort to use a rCTE.Let me know of any cases it doesn't resolve. It is possible that another UNION ALL amidst the loop will take care of it.Thu, 29 Nov 2012 23:25:24 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]mickyT (11/29/2012)[/b][hr][quote][b]dwain.c (11/29/2012)[/b][hr]To all who participated in this thread:You may wish to review this new article:[url]http://www.sqlservercentral.com/articles/String+Manipulation/94365/[/url]It provides a good utility function for solving this case and many other similar ones. Thanks to the OP for being the inspiration for the article![/quote]Thanks DwainI've already had a look at it and I am going to have a really good read through it soon. Even though I haven't had a chance to dig into the functionality and digest it properly I was impressed with the methods you came up with. Great article:-)[/quote]Thanks for noticing and reading it, and the praise!By all means leave some comments in the discussion thread and don't forget to rate the article!Thu, 29 Nov 2012 23:23:23 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxHiThe following recursive option might work. It walks up and down from the queried invoice and joins the results.[code="sql"]DECLARE @invoiceID int = 3;with rListUp as ( select a.invoiceid invoiceid, a.invoiceid currentid, a.bookingid, b.invoiceid nextinvoiceid from #IDs a CROSS APPLY (SELECT invoiceid, bookingid FROM #IDs m WHERE m.bookingid = a.bookingid ) b where b.invoiceid > a.invoiceid union all select a.invoiceid, a.nextinvoiceid, a.bookingid, c.invoiceid from rListUp a CROSS APPLY (SELECT l.invoiceid, l.bookingid FROM #IDs l WHERE l.invoiceID = a.nextinvoiceid) b CROSS APPLY (SELECT m.invoiceid, m.bookingid FROM #IDs m WHERE m.bookingid = b.bookingid and m.invoiceid > a.nextinvoiceid ) c ), rListDown as ( select a.invoiceid invoiceid, a.invoiceid currentid, a.bookingid, b.invoiceid nextinvoiceid from #IDs a CROSS APPLY (SELECT invoiceid, bookingid FROM #IDs m WHERE m.bookingid = a.bookingid ) b where b.invoiceid < a.invoiceid union all select a.invoiceid, a.nextinvoiceid, a.bookingid, c.invoiceid from rListDown a CROSS APPLY (SELECT l.invoiceid, l.bookingid FROM #IDs l WHERE l.invoiceID = a.nextinvoiceid) b CROSS APPLY (SELECT m.invoiceid, m.bookingid FROM #IDs m WHERE m.bookingid = b.bookingid and m.invoiceid < a.nextinvoiceid ) c ), rList AS ( SELECT invoiceid, nextinvoiceid FROM rListUp UNION SELECT invoiceid, nextinvoiceid FROM rListDown )select invoiceid, bookingidfrom #IDs where invoiceid in ( select nextinvoiceid from rlist where invoiceID = @InvoiceID union select @InvoiceID )[/code]It could probably be prettied up a bit more and there is still a chance that recursion limits will get hit if there is a long change of invoice/bookingsThu, 29 Nov 2012 18:58:50 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]Phil Parkin (11/29/2012)[/b][hr]I wrote the above response before trying Craig's solution, which certainly does the business - thanks! Maybe this is one instance where a loop is the best solution. I'll leave it a while longer (no pun intended) before I implement it, to see whether anyone comes up with a set-based solution.[/quote]I poked at it a bit more yesterday and while I can make that process look cleaner (it's a bit disorganized) it's the best solution I have available without digging into indexes and possible shortcuts for some of the sub-data, like the BookingID list. The killer is in the self-referencing bookingIDs and the bi-directional hierarchy, where you're going both 'up and down' the chain simultaneously.Glad to help, though. :-)Thu, 29 Nov 2012 11:48:57 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxI wrote the above response before trying Craig's solution, which certainly does the business - thanks! Maybe this is one instance where a loop is the best solution. I'll leave it a while longer (no pun intended) before I implement it, to see whether anyone comes up with a set-based solution.Thu, 29 Nov 2012 02:02:41 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxFirst of all, thanks to everyone who has taken the time to think about this problem and contribute. I spent over an hour blowing recursion limits left, right and centre yesterday without getting anywhere close, so I appreciate the input.I was excited to see Alan's solution working perfectly with my data, only to test it out using Craig's alternative (and valid) data and see it not work quite so well ...It remains a problem for us here, which for the moment we are dealing with by hard-coding multiple recursion levels. But we have examples where the actual number of recursions needed to scrape all the details together is greater than what we have coded. So any further input is most welcome.ThanksPhilThu, 29 Nov 2012 01:56:02 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]thava (11/29/2012)[/b][hr]how about this one [/quote]It can't go backwards from 3 to get back to 1 because of the limitor to reduce duplication and endless recursion. It's similar to the issue above in the hierarchy model. You need to be able to go after either InvoiceID 3 or InvoiceID 1 and get the same result list.Thu, 29 Nov 2012 01:16:28 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxhow about this one [code]SET @InvoiceID = 1;WITH rCTE AS( SELECT InvoiceID, BookingID, 1 AS HierarchyLevel FROM #IDs WHERE InvoiceID = @InvoiceID UNION ALLSELECT IDs.InvoiceID, ids2.BookingID, rCTE.HierarchyLevel + 1 AS HierarchyLevel FROM rCTE JOIN #IDs ids ON IDs.BookingID = rCTE.BookingID AND ids.InvoiceId <> rcte.InvoiceID INNER JOIN #IDs ids2 ON ids2.InvoiceId = ids.InvoiceID WHERE rCTE.InvoiceID<ids2.InvoiceId)select * from Rcte[/code]Thu, 29 Nov 2012 00:12:29 GMTthavahttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]Alan.B (11/28/2012)[/b][hr][quote][b]Evil Kraig F (11/28/2012)[/b][hr]This is NOT pretty, but it IS functional.[/quote]I am trying to understand why went with a loop vs a set-based approach. I posted a more set based method earlier that gets the same results notably faster. I am not trying to be confrontational, I'm just trying to understand your approach or what I did wrong.[/quote][strike]@Alan.b If i look at your method correctly it misses InvoiceID 3.[/strike]@Kraig your right about the issues with my method that you cant go both directions up the tree which i had forgotten about. (dont work with hierarchies much)EDIT: i sat to long to hit the post button so struck what was said before the post.Wed, 28 Nov 2012 15:27:27 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]Evil Kraig F (11/28/2012)[/b][hr][quote][b]Alan.B (11/28/2012)[/b][hr][quote][b]Evil Kraig F (11/28/2012)[/b][hr]This is NOT pretty, but it IS functional.[/quote]I am trying to understand why went with a loop vs a set-based approach. I posted a more set based method earlier that gets the same results notably faster. I am not trying to be confrontational, I'm just trying to understand your approach or what I did wrong.[/quote]Whoops, my apologies Alan. I didn't realize you were code complete. This comment:[quote][b]Alan.B (11/28/2012)[/b][hr]This is a bit tricky and I am still working on this. Here's what I have thusfar...[/quote]Misled me to believe you were still working on your solution and I breezed over it before going back to trying to force the rCTE to work.However, you will miss recursive chains. For example, I've adjusted the inclusion set here:[code="sql"]INSERT #IDs VALUES (1,9), (1,10), (1,11), (2,11), (2,12), (3,12), (3,13), (4,14), (5,14)GO[/code]You'll notice that your code will no longer pick up Invoice 3 because You need to go from 1->2 via Booking 11, then 2->3 via Booking 12.[/quote]Ahhhh.... Now I see what I was doing wrong; I knew I was missing something. Thanks & nice work.Wed, 28 Nov 2012 15:20:06 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]Alan.B (11/28/2012)[/b][hr][quote][b]Evil Kraig F (11/28/2012)[/b][hr]This is NOT pretty, but it IS functional.[/quote]I am trying to understand why went with a loop vs a set-based approach. I posted a more set based method earlier that gets the same results notably faster. I am not trying to be confrontational, I'm just trying to understand your approach or what I did wrong.[/quote]Whoops, my apologies Alan. I didn't realize you were code complete. This comment:[quote][b]Alan.B (11/28/2012)[/b][hr]This is a bit tricky and I am still working on this. Here's what I have thusfar...[/quote]Misled me to believe you were still working on your solution and I breezed over it before going back to trying to force the rCTE to work.However, you will miss recursive chains. For example, I've adjusted the inclusion set here:[code="sql"]INSERT #IDs VALUES (1,9), (1,10), (1,11), (2,11), (2,12), (3,12), (3,13), (4,14), (5,14)GO[/code]You'll notice that your code will no longer pick up Invoice 3 because You need to go from 1->2 via Booking 11, then 2->3 via Booking 12.Wed, 28 Nov 2012 15:06:44 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]Evil Kraig F (11/28/2012)[/b][hr]This is NOT pretty, but it IS functional.[/quote]I am trying to understand why went with a loop vs a set-based approach. I posted a more set based method earlier that gets the same results notably faster. I am not trying to be confrontational, I'm just trying to understand your approach or what I did wrong.Wed, 28 Nov 2012 15:00:19 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxThis is NOT pretty, but it IS functional.[code="sql"]--am trying to write a query against a table of invoices, returning all relevant rows for a particular InvoiceId. Heres some sample data:--Create temp table to hold the dummy dataif object_id('tempdb..#IDs') is not nulldrop table #IDsif object_id('tempdb..#TempStore') is not nulldrop table #TempStorecreate table #IDs (InvoiceId int not null,BookingId int not null) on [PRIMARY]goalter table #IDs add constraint PK_IDs primary key clustered ( InvoiceId ,BookingId ) with ( STATISTICS_NORECOMPUTE = off ,IGNORE_DUP_KEY = off ,ALLOW_ROW_LOCKS = on ,ALLOW_PAGE_LOCKS = on ) on [PRIMARY]goinsert #IDs ( InvoiceId, BookingId) select * from (values (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)) data(InvoiceId,BookingId)select * from #IDsDECLARE @InvoiceID INT, @Rowcount INTSELECT @InvoiceID = 1, @Rowcount = 1CREATE TABLE #TempStore (InvoiceID INT, BookingID INT)INSERT INTO #TempStoreSELECT InvoiceID, BookingIDFROM #IDsWHERE InvoiceID = @InvoiceID-- Set this here, might as well not hit the loop if no records to work from.SELECT @Rowcount = @@ROWCOUNTWHILE @Rowcount <> 0BEGIN INSERT INTO #TempStore SELECT ids2.InvoiceID, ids2.BookingID FROM #IDs JOIN (SELECT DISTINCT BookingID FROM #TempStore) AS drv ON #IDs.BookingID = drv.BookingID JOIN #IDs AS ids2 ON #IDs.InvoiceID = ids2.InvoiceID WHERE #IDs.InvoiceID NOT IN (SELECT DISTINCT InvoiceID FROM #TempStore) SET @Rowcount = @@ROWCOUNTENDSELECT * FROM #TempStore[/code]Wed, 28 Nov 2012 14:35:35 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspx[quote][b]capnhector (11/28/2012)[/b][hr]What i came up with was to turn it into an adjacency list hierarchy and then we can recourse through that. of course i could be completly off base here but here is the code to turn it into the adjacency list[/code][/quote]The only problem I have with that solution method is that you can't traverse in both directions. IE: Pulling Invoice 3 won't get you back to 1 (or vice versa) depending on the parenting.The biggest problem with this recursion is that it's self-joining. For those curious:[code="sql"]--am trying to write a query against a table of invoices, returning all relevant rows for a particular InvoiceId. Heres some sample data:--Create temp table to hold the dummy dataif object_id('tempdb..#IDs') is not nulldrop table #IDscreate table #IDs (InvoiceId int not null,BookingId int not null) on [PRIMARY]goalter table #IDs add constraint PK_IDs primary key clustered ( InvoiceId ,BookingId ) with ( STATISTICS_NORECOMPUTE = off ,IGNORE_DUP_KEY = off ,ALLOW_ROW_LOCKS = on ,ALLOW_PAGE_LOCKS = on ) on [PRIMARY]goinsert #IDs ( InvoiceId, BookingId) select * from (values (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)) data(InvoiceId,BookingId)select * from #IDsDECLARE @InvoiceID INTSET @InvoiceID = 1;WITH rCTE AS(SELECT InvoiceID, BookingID, 1 AS HierarchyLevelFROM #IDsWHERE InvoiceID = @InvoiceIDUNION ALL SELECT #IDs.InvoiceID, ids2.BookingID, rCTE.HierarchyLevel + 1 AS HierarchyLevel FROM rCTE JOIN #IDs ON #IDs.BookingID = rCTE.BookingID JOIN #IDs AS ids2 ON #IDs.InvoiceID = ids2.InvoiceIDWHERE rCTE.HierarchyLevel + 1 )SELECT * FROM rCTE[/code]I'm trying to work out a way to exclude previously included invoices from the detection list but you can't double-reference the CTE in the recursion, so NOT IN (SELECT) clauses and the like are out. I'm actually thinking this may need to be looped as a baseline and then worked on from there.Wed, 28 Nov 2012 14:27:39 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxWhat i came up with was to turn it into an adjacency list hierarchy and then we can recourse through that. of course i could be completly off base here but here is the code to turn it into the adjacency list[code="sql"]CREATE TABLE #IDsParents (InvoiceId int not null,BookingId int not null,ParentInvoiceID INT)insert #IDsParents ( InvoiceId, BookingId) select * from (values (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)) data(InvoiceId,BookingId);WITH Parent AS(SELECT a.InvoiceId, b.InvoiceId AS Parent FROM #IDsParents a LEFT JOIN #IDsParents b ON a.BookingId = b.BookingId AND a.InvoiceId > b.InvoiceIdWHERE b.InvoiceId IS NOT NULL)UPDATE b SET ParentInvoiceID = a.Parent FROM Parent a RIGHT JOIN #IDsParents b ON a.InvoiceId = b.InvoiceIdSELECT * FROM #IDsParents[/code]Wed, 28 Nov 2012 14:15:55 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxThis is a bit tricky and I am still working on this. Here's what I have thusfar...[code="sql"]--Create temp table to hold the dummy dataif object_id('tempdb..#IDs') is not null drop table #IDsCREATE TABLE #IDs ( InvoiceId int not null, BookingId int not null, primary key clustered (InvoiceId, BookingId));INSERT #IDs VALUES (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)GODECLARE @val int = 1;WITH x AS( SELECT i.InvoiceId, i.BookingId from ( SELECT InvoiceId, BookingId from #IDs WHERE InvoiceId = @val ) a JOIN #IDs i ON a.BookingId = i.BookingId)SELECT DISTINCT i.* FROM xRIGHT JOIN #IDs i ON x.InvoiceId = i.InvoiceIdWHERE x.InvoiceId IS NOT NULL[/code]Wed, 28 Nov 2012 14:11:40 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxCorrect.Wed, 28 Nov 2012 13:24:04 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxIf there is a row (3,14) would all your sample data then be returned because of (4,14) and (5,14)? from your description i think that is correct. just trying to wrap my head around it.Wed, 28 Nov 2012 13:21:09 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1390030-392-1.aspxHi - can't seem to get my head around this one, so any assistance is welcome. I am trying to write a query against a table of invoices, returning all relevant rows for a particular InvoiceId. Here's some sample data:[code="sql"]--Create temp table to hold the dummy dataif object_id('tempdb..#IDs') is not nulldrop table #IDscreate table #IDs (InvoiceId int not null,BookingId int not null) on [PRIMARY]goalter table #IDs add constraint PK_IDs primary key clustered ( InvoiceId ,BookingId ) with ( STATISTICS_NORECOMPUTE = off ,IGNORE_DUP_KEY = off ,ALLOW_ROW_LOCKS = on ,ALLOW_PAGE_LOCKS = on ) on [PRIMARY]goinsert #IDs ( InvoiceId, BookingId) select * from (values (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)) data(InvoiceId,BookingId)select * from #IDs[/code]Now, imagine that we're interested in returning data for InvoiceId 1.What I would like to return is this:[code="sql"]SELECT *FROM (VALUES(1,9),(1,10),(1,11),(2,11),(3,11),(3,12),(3,13)) x([InvoiceId],[BookingId])[/code]The 'twist' should now be obvious: InvoiceId 1 has BookingIds which relate to other invoices, which in turn may have BookingIds which relate to other invoices. I want to return all related rows.Wed, 28 Nov 2012 11:54:50 GMTPhil Parkin