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


Many to Many Recursive CTE


Many to Many Recursive CTE

Author
Message
PugMaster
PugMaster
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 366
Hi

I have posted in this section because it is a 2012 database I am working against and could not find a TSQL general section, so I apologize if I have posted in the wrong place.

I have the following table:-

CREATE TABLE [dbo].[TransactionComponents](
[pkTransactionComponent] [int] IDENTITY(1,1) NOT NULL,
[pkTransactionID] [int] NOT NULL,
[ComponentID] [int] NULL
) ON [PRIMARY]

With the following data:-

INSERT [dbo].[TransactionComponents]([pkTransactionID], [ComponentID])
SELECT 1,5
UNION SELECT 1,6
UNION SELECT 1,7
UNION SELECT 1,8
UNION SELECT 1,9
UNION SELECT 1,10
UNION SELECT 10,2
UNION SELECT 2,11
UNION SELECT 2,12
UNION SELECT 3,4

pkTransactionID and ComponentID both link to the same column on another table this enables a many to many relationship, what I need to figure out is a complete tree of relationships from one of the ID's in it. I think I need to write a recursive CTE to achieve this but I am not entirely sure how to write it. Below is my attempt:-


DECLARE @ID INT
SET @ID = 1;
WITH
cteTxHeirachy (TxID, RelTxID, TxLevel)
AS
(
--Anchor
SELECT pkTransactionID, ComponentID, 1
FROM [dbo].[TransactionComponents]
WHERE ComponentID = @ID
OR pkTransactionID = @ID
--Recursive
UNION ALL
SELECT pkTransactionID, ComponentID, cteTxH.TxLevel + 1
FROM [dbo].[TransactionComponents] Tc
INNER JOIN cteTxHeirachy cteTxH
ON TC.ComponentID = cteTxH.TxID
--UNION ALL
--SELECT pkTransactionID, ComponentID, cteTxH.TxLevel + 1
--FROM [dbo].[TransactionComponents] Tc
--INNER JOIN cteTxHeirachy cteTxH
--ON TC.pkTransactionID = cteTxH.RelTxID
)
SELECT DISTINCT * FROM cteTxHeirachy
option (maxrecursion 0)

This returns:-

1 5
1 6
1 7
1 8
1 9
1 10

But the following are missing:-

10 2
2 11
2 12

3 and 4 should not be returned. I figured if I added the code that is commented out in the CTE that should give me everything but I think I get caught in an infinite loop.

Any advise/help much appreciated.

Thanks in advance,
Paul

Cheers, Paul
Sowbhari
Sowbhari
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2194 Visits: 1488
Try the below code...

DECLARE @TransactionComponents TABLE
(
pkTransactionComponent int IDENTITY(1,1) NOT NULL,
pkTransactionID int NOT NULL,
ComponentID int NULL
)
INSERT INTO @TransactionComponents(pkTransactionID, ComponentID)
SELECT 1,5
UNION SELECT 1,6
UNION SELECT 1,7
UNION SELECT 1,8
UNION SELECT 1,9
UNION SELECT 1,10
UNION SELECT 10,2
UNION SELECT 2,11
UNION SELECT 2,12
UNION SELECT 3,4
DECLARE @Id INT
SELECT @Id = 1
;WITH Hierarchy AS
(
SELECT * FROM @TransactionComponents
WHERE pktransactionid = @Id
UNION ALL
SELECT A.* FROM @TransactionComponents AS A
INNER JOIN Hierarchy AS B ON A.pktransactionid = B.componentid
)
SELECT * FROM Hierarchy


Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61501 Visits: 17954
You have your join condition backwards on the recursive portion of your cte.


ON cteTxH.ComponentID = TC.pkTransactionID



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
PugMaster
PugMaster
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 366
Hi

Many thanks for the reply, one thing i did not demonstrate in my sample (sorry, me being stupid) is that the parent or child could appear in either column, which makes it a little different so if i run this:-

DECLARE @TransactionComponents TABLE
(
pkTransactionComponent int IDENTITY(1,1) NOT NULL,
pkTransactionID int NOT NULL,
ComponentID int NULL
)
INSERT INTO @TransactionComponents(pkTransactionID, ComponentID)
SELECT 1,5
UNION SELECT 1,6
UNION SELECT 1,7
UNION SELECT 1,8
UNION SELECT 1,9
UNION SELECT 1,10
UNION SELECT 10,2
UNION SELECT 2,11
UNION SELECT 2,12
UNION SELECT 3,4
UNION SELECT 13,2

DECLARE @Id INT
SELECT @Id = 1
;WITH Hierarchy AS
(
SELECT * FROM @TransactionComponents
WHERE pktransactionid = @Id
UNION ALL
SELECT A.* FROM @TransactionComponents AS A
INNER JOIN Hierarchy AS B ON A.pktransactionid = B.componentid
)
SELECT * FROM Hierarchy

It should return 10 rows (13, 2) but only returns 9, this is where I hit the problem, if I add an extra Union ALL catch these I end up in an infinite loop.

Kind Regards
Paul

Cheers, Paul
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61501 Visits: 17954
PugMaster (2/18/2014)
Hi

Many thanks for the reply, one thing i did not demonstrate in my sample (sorry, me being stupid) is that the parent or child could appear in either column, which makes it a little different so if i run this:-



A little strange but not outside of the realm of feasible. Just run two different queries. One where the parent is in the first column and another query where the parent is in the other column. You may need to create a temp table and insert the results from each query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
PugMaster
PugMaster
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 366
Hi Sean

Unfortunately it is a system I have inherited and the table holds 6.8 million rows there is another column that flags if the second column is a child ID or a parent ID, I'll have a go with a temp table and see what happens.

Kind Regards
Paul

Cheers, Paul
stormsentinelcammy
stormsentinelcammy
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 807
I second your suggestion, totally go with a temp table, take a small sample size (if you can) of the 6.8 million rows so that you can see results quicker...good luck
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3434 Visits: 3149
PugMaster (2/19/2014)
Hi Sean

Unfortunately it is a system I have inherited and the table holds 6.8 million rows there is another column that flags if the second column is a child ID or a parent ID, I'll have a go with a temp table and see what happens.

Kind Regards
Paul


If you have a field in the same table indicating which field is the parent, you can begin with a CTE that gets all the data the same (select field1, field2 where the flag is 0 union all to select field2, field1 where the flag is 1).

Then you don't have to do your recursion twice.
Sowbhari
Sowbhari
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2194 Visits: 1488
This might not be the best solution...but it works on your sample data.

DECLARE @Id INT
SELECT @Id = 1
;WITH Hierarchy AS
(
SELECT * FROM @TransactionComponents
WHERE pktransactionid = @Id
UNION ALL
SELECT * FROM @TransactionComponents
WHERE componentid = @Id
UNION ALL
SELECT A.* FROM @TransactionComponents AS A
INNER JOIN Hierarchy AS B ON A.pktransactionid = B.componentid
),
Hierarchy2 AS
(
SELECT * FROM Hierarchy UNION ALL
SELECT A.* FROM @TransactionComponents AS A
INNER JOIN Hierarchy AS B ON B.pktransactionid = A.componentid
)
SELECT DISTINCT * FROM Hierarchy2


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