February 17, 2014 at 7:24 am
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:-
15
16
17
18
19
110
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
February 17, 2014 at 7:51 am
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
February 17, 2014 at 7:57 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 18, 2014 at 1:30 am
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
February 18, 2014 at 9:09 am
PugMaster (2/18/2014)
HiMany 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2014 at 5:30 am
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
February 19, 2014 at 7:31 am
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
February 19, 2014 at 8:13 am
PugMaster (2/19/2014)
Hi SeanUnfortunately 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.
February 19, 2014 at 8:49 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply