SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursion with a Twist


Recursion with a Twist

Author
Message
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18131 Visits: 20388
Hi - 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:


--Create temp table to hold the dummy data

if object_id('tempdb..#IDs') is not null
drop table #IDs

create table #IDs (
InvoiceId int not null
,BookingId int not null
) on [PRIMARY]

go

alter 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]
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



Now, imagine that we're interested in returning data for InvoiceId 1.

What I would like to return is this:

SELECT *
FROM (
VALUES
(1,9),
(1,10),
(1,11),
(2,11),
(3,11),
(3,12),
(3,13)) x
([InvoiceId],[BookingId])



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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1327 Visits: 1789
If 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.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18131 Visits: 20388
Correct.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5047 Visits: 7697
This is a bit tricky and I am still working on this. Here's what I have thusfar...


--Create temp table to hold the dummy data
if object_id('tempdb..#IDs') is not null
drop table #IDs

CREATE 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)
GO

DECLARE @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 x
RIGHT JOIN #IDs i ON x.InvoiceId = i.InvoiceId
WHERE x.InvoiceId IS NOT NULL



-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1327 Visits: 1789
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

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.InvoiceId
WHERE b.InvoiceId IS NOT NULL
)

UPDATE b SET ParentInvoiceID = a.Parent
FROM Parent a
RIGHT JOIN #IDsParents b
ON a.InvoiceId = b.InvoiceId

SELECT * FROM #IDsParents




For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8535 Visits: 7660
capnhector (11/28/2012)
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]


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:

--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 data

if object_id('tempdb..#IDs') is not null
drop table #IDs

create table #IDs (
InvoiceId int not null
,BookingId int not null
) on [PRIMARY]

go

alter 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]
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

DECLARE @InvoiceID INT

SET @InvoiceID = 1

;WITH rCTE AS
(SELECT
InvoiceID,
BookingID,
1 AS HierarchyLevel
FROM
#IDs
WHERE
InvoiceID = @InvoiceID
UNION 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.InvoiceID
WHERE
rCTE.HierarchyLevel + 1
)

SELECT * FROM rCTE



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.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8535 Visits: 7660
This is NOT pretty, but it IS functional.


--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 data

if object_id('tempdb..#IDs') is not null
drop table #IDs


if object_id('tempdb..#TempStore') is not null
drop table #TempStore


create table #IDs (
InvoiceId int not null
,BookingId int not null
) on [PRIMARY]

go

alter 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]
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

DECLARE @InvoiceID INT,
@Rowcount INT

SELECT @InvoiceID = 1,
@Rowcount = 1

CREATE TABLE #TempStore
(InvoiceID INT, BookingID INT)

INSERT INTO #TempStore
SELECT
InvoiceID, BookingID
FROM
#IDs
WHERE
InvoiceID = @InvoiceID

-- Set this here, might as well not hit the loop if no records to work from.
SELECT @Rowcount = @@ROWCOUNT

WHILE @Rowcount <> 0
BEGIN
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 = @@ROWCOUNT
END

SELECT * FROM #TempStore




- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5047 Visits: 7697
Evil Kraig F (11/28/2012)
This is NOT pretty, but it IS functional.


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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8535 Visits: 7660
Alan.B (11/28/2012)
Evil Kraig F (11/28/2012)
This is NOT pretty, but it IS functional.


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.


Whoops, my apologies Alan. I didn't realize you were code complete. This comment:
Alan.B (11/28/2012)
This is a bit tricky and I am still working on this. Here's what I have thusfar...

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:
INSERT #IDs VALUES (1,9), (1,10), (1,11), (2,11), (2,12), (3,12), (3,13), (4,14), (5,14)
GO



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.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5047 Visits: 7697
Evil Kraig F (11/28/2012)
Alan.B (11/28/2012)
Evil Kraig F (11/28/2012)
This is NOT pretty, but it IS functional.


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.


Whoops, my apologies Alan. I didn't realize you were code complete. This comment:
Alan.B (11/28/2012)
This is a bit tricky and I am still working on this. Here's what I have thusfar...

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:
INSERT #IDs VALUES (1,9), (1,10), (1,11), (2,11), (2,12), (3,12), (3,13), (4,14), (5,14)
GO



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.


Ahhhh.... Now I see what I was doing wrong; I knew I was missing something.

Thanks & nice work.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search