Simple CTE recursive query

Question

I am sorry to bother with such a simple question, but I decided to learn CTE recursive queries and I am unable to get my query work even after scoping many sources and threads. So I am humbly asking for pointing out my mistake(s).

Here is a part of table I am querying:

ID        ContainerInstanceID  ItemID      ContentContainerInstanceID
--------- -------------------- ----------- --------------------------
73        40                   NULL        41        
69        40                   23885       NULL
68        40                   29683       NULL
67        40                   29686       NULL
72        41                   27392       NULL
71        41                   29235       NULL
70        41                   29213       NULL

I assembled this simple CTE query:

;WITH ContainerContent_CTE(InstanceID,ItemID,ContentContainerInstanceID) AS
 (
  -- ROOT set accordig to input parameter
  SELECT ContainerInstanceID,SCA.ItemID,SCA.ContentContainerInstanceID 
  FROM StockContainerAssignments as SCA 
  WHERE SCA.ContainerInstanceID = 40  -- input parameter

  UNION ALL

  -- recursive data
  SELECT ContainerInstanceID,SCA2.ItemID,SCA2.ContentContainerInstanceID 
  FROM ContainerContent_CTE AS CC
  JOIN StockContainerAssignments as SCA2 on CC.InstanceID = SCA2.ContentContainerInstanceID
  )
SELECT * FROM ContainerContent_CTE;

What I am trying to do is to take a top-level container, in this example it has ID = 40, which is my input parameter. Then, I try to connect other levels by linking ContainerInstanceID with ContentContainerInstanceID. In my example it is not null ar row ID = 73. This should add another 3 rows to my result set (so it should look similar to the example data I presented above), but I still get only top level rows:

InstanceID   ItemID  ContentContainerInstanceID
-----------  ----------- --------------------------
40           29686       NULL
40           29683       NULL
40           23885       NULL
40           NULL        41        

I appreciate hints to help me stumble over this subject.

Answer 1

You just had a few little things out of place. This should work for you.

with ContainerContent_CTE as
(
    select SCA.ContainerInstanceID
        ,SCA.ItemID
        ,SCA.ContentContainerInstanceID 
    FROM StockContainerAssignments as SCA 
    WHERE SCA.ContainerInstanceID = 40  -- input parameter

    UNION ALL 

    select SCA.ContainerInstanceID
        ,SCA.ItemID
        ,SCA.ContentContainerInstanceID 
    FROM StockContainerAssignments as SCA
    inner join ContainerContent_CTE cte on cte.ContentContainerInstanceID = SCA.ContainerInstanceID
)

select *
from ContainerContent_CTE