Page 1 of 1
Connecting ac_OrderItems and ac_Categories via SQL query
Posted: Mon Feb 22, 2016 6:38 am
by 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?
Re: Connecting ac_OrderItems and ac_Categories via SQL query
Posted: Mon Feb 22, 2016 10:32 am
by 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.
Re: Connecting ac_OrderItems and ac_Categories via SQL query
Posted: Tue Feb 23, 2016 4:25 am
by 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.