Extract categories for products

[Tea-Dev]

Hi.
I am trying to extract the categories for all the products identified in ac_Products. I can see the ac_Categories, but unable to join these 2 tables together.
Would anyone be able to provide a select statement where we can get the products and the categories they belong to (ac_products.productId, ac_Categories.Name, ....).

[mazhar]

you need to look into ac_CatalogNode table. The SQL could be something like

Code: Select all

SELECT CN.CategoryId, P.Name
JOIN ac_CatalogNodes AS CN
JOIN ac_Products AS WP ON CN.CatalogNodeId = P.ProductId
WHERE CN.CatalogNodeTypeId = 1

[tripleW]

This worked for me with a couple adjustments.

SELECT CN.CategoryId, P.Name
FROM ac_CatalogNodes AS CN
JOIN ac_Products AS P ON CN.CatalogNodeId = P.ProductId
WHERE CN.CatalogNodeTypeId = 1