enter image description hereI'm using Magento 1.9, We have a requirement to show the product details with category tree heirarchy. Have searched this in google and haven't find any answers.

I have to create a report(using a BI tool) ,So i need to find a way to do this in pure SQL.

Does anyone have any suggestions on how to achieve this?

Below is the expected sample of expected output.There are 3 levels of categories and main category. So i want the Products which is the lowest level to be under the corresponding categories in a tree structure.

  • Which SQL Server with which version are you using? If you'd rather not tell, does it support CTE (common table expressions)?
    – R. Horber
    Commented Apr 8, 2020 at 11:41

1 Answer 1


As the nesting level of the categories is limited, an approach with joins can be taken. Otherwise, a CTE would be a better approach.

My suggestion is based on the product category association. For each category, the parent is joined and the parent of the parent. The select gets the name of the category depending on the nesting level of the association.

        -- association level 1 category
        WHEN parent.entity_id IS NULL THEN category.name
        -- association level 2 category
        WHEN grand_parent.entity_id IS NULL THEN parent.name
        -- association level 3 category
        ELSE grand_parent.name
    END AS level_1,
        -- association level 1 category
        WHEN parent.entity_id IS NULL THEN NULL
        -- association level 2 category
        when grand_parent.entity_id IS NULL THEN category.name
        -- association level 3 category
        ELSE parent.name
    END AS level_2,
        -- association level 3 category
        WHEN grand_parent.entity_id IS NOT NULL THEN category.name
        -- association level 2 or 1 category
        ELSE NULL
    END AS level_3,
FROM catalog_category_product AS ccp
    JOIN magento_dev.catalog_product_flat_1 AS product
        ON ccp.product_id = product.entity_id
    JOIN catalog_category_flat_store_1 AS category
        ON ccp.category_id = category.entity_id
            AND category.is_active = 1
    LEFT JOIN catalog_category_flat_store_1 AS parent
        ON category.parent_id = parent.entity_id
            AND parent.level >= 2
    LEFT JOIN catalog_category_flat_store_1 AS grand_parent
        ON parent.parent_id = grand_parent.entity_id
            AND grand_parent.level >= 2

As you see, the flat tables are used (to access the category name directly and not with another join), which requires up to date indexes. During my tests, the table catalog_category_product was also updated only through reindexing.

I'm not sure if this is what you expected/requested, but I hope it gives you a hint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.