Many to Many Recursive CTE

  • 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

  • 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

  • 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/

  • 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

  • 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 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/

  • 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

  • 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

  • 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.

  • 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