title: SQL iteration though several levels of parent/child hierarchy
body: <p>Background: I have 3 MSSQLServer tables: product, category, product_category_mapping.  there are up to 5 levels of categories above the product and this data is held in the mapping table.
  What I want to do is iterate though the products and for every product category level, create a new mapping for that product against the current level.</p>
  
  <p>I started with this query, and thought I could develop it by putting cursors at each category stage, where I would perform the insert. Once I started found it hard to get my head around, and now I'm wondering if this is even the best way to go about it - can someone show me a better way of doing this? </p>
  
  <pre><code>SELECT DISTINCT p.ProductId,p.name
  FROM Nop_Product p 
  INNER JOIN Nop_Product_Category_Mapping cm ON p.ProductId = cm.ProductID
  INNER JOIN Nop_Category c  ON cm.CategoryID = c.CategoryID
  WHERE c.CategoryID in (
       --next level
      SELECT DISTINCT Nop_Category.CategoryID
       FROM Nop_Product_Category_Mapping
      INNER JOIN Nop_Category  ON Nop_Product_Category_Mapping.CategoryID = Nop_Category.CategoryID
      WHERE Nop_Category.ParentCategoryID in (
      -- next level
          SELECT DISTINCT Nop_Category.CategoryID
           FROM Nop_Product_Category_Mapping
          INNER JOIN Nop_Category  ON Nop_Product_Category_Mapping.CategoryID = Nop_Category.CategoryID
          WHERE Nop_Category.ParentCategoryID in (
          -- next level
              SELECT DISTINCT Nop_Category.CategoryID
               FROM Nop_Product_Category_Mapping
              INNER JOIN Nop_Category  ON Nop_Product_Category_Mapping.CategoryID = Nop_Category.CategoryID
              WHERE Nop_Category.ParentCategoryID in (
                  -- next level
                  SELECT DISTINCT Nop_Category.CategoryID
                   FROM Nop_Product_Category_Mapping
                  INNER JOIN Nop_Category  ON Nop_Product_Category_Mapping.CategoryID = Nop_Category.CategoryID
                  WHERE Nop_Category.ParentCategoryID in (
                  --top level categories
                      SELECT Nop_Category.CategoryID
                      FROM Nop_Product_Category_Mapping 
                      INNER JOIN Nop_Category  ON Nop_Product_Category_Mapping.CategoryID = Nop_Category.CategoryID
                      WHERE Nop_Category.ParentCategoryID = 0)))))
  </code></pre>