title: SQL Server recursive CTE query to find all items belonging to all categories and their descendants
body: <p>I am a bit confused trying to use a recursive CTE to list all the items in a product catalogue for all the categories <em>and</em> their respective parent categories that each item belongs to.</p>
  
  <p>The tables are really simple...</p>
  
  <ul>
  <li>Table <code>Category</code>; Columns: Id, ParentID, Title</li>
  <li>Table <code>itemCategory</code>; Columns: ItemID, CategoryID</li>
  </ul>
  
  <p>I am just struggling to work out how to get the results I want. My best attempt is not right:</p>
  
  <pre><code>WITH
  CTE (itemID, categoryID, title) AS (
      SELECT itemID, categoryID, title
      FROM itemcategory
      INNER JOIN category ON category.ID = itemcategory.categoryID
      UNION ALL
      SELECT iI.ItemID, iI.categoryID, i.title
      FROM itemcategory iI INNER JOIN category i ON i.ID = iI.categoryID
      INNER JOIN CTE ON CTE.categoryID = i.ParentID)
  SELECT * FROM CTE
  </code></pre>
  
  <p>I have a similar query which counts the number of items under each category:</p>
  
  <pre><code>WITH cte_count_category(id, parentid, c)
          AS (SELECT c1.id,
                  c1.parentid,
                  (SELECT Count(*)
                      FROM   (SELECT DISTINCT itemid
                              FROM   itemcategory AS iI
                              WHERE  iI.categoryid = c1.id) AS t1) AS c
              FROM   category AS c1
              UNION ALL
              SELECT c2.id,
                  c2.parentid,
                  d.c
              FROM   category c2
                  INNER JOIN cte_count_category d
                          ON c2.id = d.parentid)
  SELECT cte_count_category.id,
          cte_count_category.parentid,
          title,
          Sum(c)     itemCount
  FROM   cte_count_category
          LEFT JOIN category
                  ON category.id = cte_count_category.id
  GROUP  BY cte_count_category.id,
              cte_count_category.parentid,
              title
  HAVING Sum(c) &gt; 0
  ORDER  BY itemcount DESC;
  </code></pre>
  
  <p>I just can't work out how to get it to list all the items. Any help would be appreciated.</p>
  
  <p>Edit: The categories need to be n deep although it is unlikely that it will go beyond 4 levels most of the time.</p>
  
  <p>The output I am looking for is one row per itemID per category it falls under so, an item in the 'music book' category would appear under both the 'music book' category and the 'book' category.</p>