CODEWITHSUNDEEP
sqlrecursionhierarchical-data
markpsmith
markpsmith

Reputation: 4918

SQL iteration though several levels of parent/child hierarchy

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.

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? 

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)))))

Upvotes: 0

Views: 2746

Answers (1)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

If it's Microsoft SQL Server, you can try using CTE (Common Table Expressions) for recursive queries.

If it's PostgreSQL you have support for recursive queries also.

Here's how to do it in Oracle.

Upvotes: 2

Related Questions