Connecting ac_OrderItems and ac_Categories via SQL query

[kyleS2D]

I am trying to write a SQL query that will allow me to view product sales that includes categories. We are using R9(build 7670). I've looked at the AC 7 schema and understand that it is very similar to the Gold schema, but I don't see a relation between categories and products.

Is it possible to make a JOIN that will allow me to see items sold in a certain period that includes the item's category?

[jguengerich]

I think you have to go "through" ac_CatalogNodes. For products, ac_CatalogNodes.CatalogNodeTypeId = 1 and ac_CatalogNodes.CatalogNodeId = ac_Products.ProductId, and ac_CatalogNodes.CategoryId = ac_Categories.CategoryId. There may be multiple records if the product is in more than one category.

[mazhar]

As Jay suggested you can use ac_CatalogNodes table to find the categories. Following simple query will get you the categories a product belongs too.

Code: Select all

DECLARE @productId int = 33
SELECT CategoryId,Name FROM ac_Categories WHERE CategoryId IN (
SELECT CategoryId FROM ac_CatalogNodes
WHERE CatalogNodeId = @productId AND CatalogNodeTypeId = 1)

Just replace the 33 with id of product you want to find parent categories for. In your query you can either try to join or just make a separate query for every product record in report.