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 ?

Answer 1

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