Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Recursion with a Twist Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 11:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 5,047, Visits: 11,796
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1390030
Posted Wednesday, November 28, 2012 1:21 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 945, Visits: 1,771
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

Jeremy Oursler
Post #1390091
Posted Wednesday, November 28, 2012 1:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 5,047, Visits: 11,796
Correct.


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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1390094
Posted Wednesday, November 28, 2012 2:11 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:18 AM
Points: 562, Visits: 2,617
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1390130
Posted Wednesday, November 28, 2012 2:15 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 945, Visits: 1,771
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

Jeremy Oursler
Post #1390132
Posted Wednesday, November 28, 2012 2:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 6,237, Visits: 7,392
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
Post #1390135
Posted Wednesday, November 28, 2012 2:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 6,237, Visits: 7,392
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
Post #1390138
Posted Wednesday, November 28, 2012 3:00 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:18 AM
Points: 562, Visits: 2,617
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1390155
Posted Wednesday, November 28, 2012 3:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 6,237, Visits: 7,392
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
Post #1390159
Posted Wednesday, November 28, 2012 3:20 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:18 AM
Points: 562, Visits: 2,617
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1390166
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse