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

Many to Many Recursive CTE Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 7:24 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:35 AM
Points: 28, Visits: 266
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
Post #1542114
Posted Monday, February 17, 2014 7:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 1,078, Visits: 869
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

Post #1542136
Posted Monday, February 17, 2014 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's 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)
Post #1542138
Posted Tuesday, February 18, 2014 1:30 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:35 AM
Points: 28, Visits: 266
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
Post #1542409
Posted Tuesday, February 18, 2014 9:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's 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)
Post #1542634
Posted Wednesday, February 19, 2014 5:30 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:35 AM
Points: 28, Visits: 266
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
Post #1542935
Posted Wednesday, February 19, 2014 7:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 3:57 PM
Points: 120, Visits: 677
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
Post #1542996
Posted Wednesday, February 19, 2014 8:13 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 612, Visits: 2,119
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.

Post #1543050
Posted Wednesday, February 19, 2014 8:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 1,078, Visits: 869
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

Post #1543077
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse