Recursive CTE with tree hierarchy SQL Server

Question

I need to use recursive with SQL Server, but i don't know how use it with my hierarchy tree.

I need some help for creating my query and know if it's possible with CTE Recursion.

My example :

I have two tables : piece (piece_id) and piece_equivalence(piece1_id, piece2_id)

First, i need to get all the piece from the first table :

SELECT DISTINCT p.record_id FROM piece p

Secondly, i need to check if the piece exists in the second table (piece1_id or piece2_id)

SELECT DISTINCT p.record_id           
        FROM piece p
        inner join piece_equivalence pe 
ON (pe.piece1_id = p.record_id OR pe.piece2_id = p.record_id)

Thirdly, if the piece exist, I need to check the piece1_id or piece2_id. This ID can have an equivalence too. So I will check the second step too with my piece1_id or piece2_id.

Currently I did recursive calling the same function with parameter piece1 or piece2.

Graphical view with nodes :

                                                        piece_id
                                                        ___/ \___
                                                        /         \
table : piece_equivalence               piece1_id or piece2_id    piece1_id or piece2_id
                                                /       \              /         \
table : piece_equivalence       piece1_id or piece2_id    same        same        same

Graphical with letters :

           A
       ___/ \___ ________
      /         \        \
    B            C        D
   / \          / \      / \
  D   E       F   B     E   G
 /            /
G             H

A : piece B, C, D, E, F, G, H are equivalences.

WARNING : I need to stock all piece with their equivalence in a temp table. For avoiding duplicate entry or infinity loop we must check this temp table the data exists or not.

EDIT :

I did this :

WITH pieces_CTE 
AS
(
        SELECT TOP 1 p.record_id as parent,
              case when pe.piece1_id <> p.record_id then pe.piece1_id else pe.piece2_id end as enfant,
              1 as level
        FROM piece p
        inner join piece_equivalence pe ON (pe.piece1_id = p.record_id OR pe.piece2_id = p.record_id) AND pe.pertinence = 100
        AND pe.piece1_id <> pe.piece2_id
        UNION ALL       
        SELECT c.parent, case when enfant.piece1_id <> c.parent then enfant.piece1_id else enfant.piece2_id end as enfant,
        c.level+1 
        from pieces_CTE c
        INNER JOIN piece_equivalence enfant ON (enfant.piece1_id = c.parent OR enfant.piece2_id = c.parent)
        WHERE  enfant.pertinence = 100

)

SELECT * from pieces_CTE ORDER BY parent,level,enfant
OPTION (MAXRECURSION 32767)

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

But I have a large record on it, and my query has to much records, I think it's impossible to use CTE with many redundant cycles...

But why I have the same error with TOP 1 ?

Jayasurya Satheesh · Accepted Answer · 2018-12-21 09:35:17Z

Before you start with the Recursive CTE, You need to Know Few Things

You can't use DISTINCT or UNION
You Can't Use LEFT JOIN in the Recursive part of the CTE
You need to make sure the Recursion does not end in a Dead Lock. Otherwise by the Default Recursion count of 100, the CTE Will Terminate. Please see the below Example :

DECLARE @MyData TABLE ( SeqNo INT IDENTITY(1,1), FullName VARCHAR(50), ManagerId INT ) INSERT INTO @MyData ( FullName ) VALUES('CEO')

--Insert Sub Components

   INSERT INTO @MyData    (
FullName,
ManagerId    )    SELECT
'Department Head 1',
ManagerId = SeqNo
FROM @MyData
    WHERE FullName = 'CEO'    UNION    SELECT
'Department Head 2',
ManagerId = SeqNo
FROM @MyData
    WHERE FullName = 'CEO'    UNION    SELECT
'Department Head 3',
ManagerId = SeqNo
FROM @MyData
    WHERE FullName = 'CEO'

   INSERT INTO @MyData    (
FullName,
ManagerId    )    SELECT
'Manager 1',
ManagerId = SeqNo
FROM @MyData
    WHERE FullName = 'Department Head 1'    UNION    SELECT
'Manager 2',
ManagerId = SeqNo
FROM @MyData
    WHERE FullName = 'Department Head 1'    UNION    SELECT
'Manager 3',
ManagerId = SeqNo
FROM @MyData
    WHERE FullName = 'Department Head 3'
   ;WITH CTE    AS    (
SELECT
    SeqNo,
    FullName,
    Manager = ISNULL(FullName,'')
    FROM @MyData
        WHERE ManagerId IS NULL
UNION ALL
SELECT
    MD.SeqNo,
    MD.FullName,
    Manager = ISNULL(CTE.FullName,'')
    FROM CTE
        INNER JOIN @MyData MD
            ON CTE.SeqNo = MD.ManagerId    )    SELECT
*
FROM CTE

SeqNo       FullName                                           Manager
----------- -------------------------------------------------- --------------------------------------------------
1           CEO                                                CEO
2           Department Head 1                                  CEO
3           Department Head 2                                  CEO
4           Department Head 3                                  CEO
7           Manager 3                                          Department Head 3
5           Manager 1                                          Department Head 1
6           Manager 2                                          Department Head 1

Collectives™ on Stack Overflow

Recursive CTE with tree hierarchy SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related