Page 1 of 1
Where is Product -> Category relationship stored in database
Posted: Thu Jan 14, 2010 2:36 pm
by southside2005
I'm trying to create a simply query to show list of all public (non-ghosted) products and what category they are in. I can't seem to find the relationship between ac_Products and ac_Categories to pull in the related Category names. Is there an easy way to generate this query or report?
Re: Where is Product -> Category relationship stored in database
Posted: Thu Jan 14, 2010 4:39 pm
by bradtm
Yeah I had the same issue as they didn't name this table too well. It's ac_CatalogNodes. CatalogNodeId is ProductId.
Re: Where is Product -> Category relationship stored in database
Posted: Thu Jan 14, 2010 5:34 pm
by southside2005
Awesome - exactly what I was looking for. You're right, the name doesn't lend itself to easily find that. Thank you!
Re: Where is Product -> Category relationship stored in database
Posted: Thu Feb 25, 2010 7:37 pm
by Mike718NY
I'm trying to do the same thing.
Can I steal this SQL off someone
Re: Where is Product -> Category relationship stored in database
Posted: Fri Feb 26, 2010 12:56 pm
by southside2005
This worked for me:
SELECT dbo.ac_Products.ProductId, dbo.ac_Products.Name, dbo.ac_Products.Sku, dbo.ac_Products.Description, dbo.ac_Products.InventoryModeId,
dbo.ac_Products.InStock, dbo.ac_Products.InStockWarningLevel, dbo.ac_Categories.Name AS Expr1
FROM dbo.ac_Categories INNER JOIN
dbo.ac_CatalogNodes ON dbo.ac_Categories.CategoryId = dbo.ac_CatalogNodes.CategoryId INNER JOIN
dbo.ac_Products ON dbo.ac_CatalogNodes.CatalogNodeId = dbo.ac_Products.ProductId
WHERE (dbo.ac_Products.VisibilityId = 0)