Where is Product -> Category relationship stored in database

[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?

[bradtm]

Yeah I had the same issue as they didn't name this table too well. It's ac_CatalogNodes. CatalogNodeId is ProductId.

[southside2005]

Awesome - exactly what I was looking for. You're right, the name doesn't lend itself to easily find that. Thank you!

[Mike718NY]

I'm trying to do the same thing.
Can I steal this SQL off someone

[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)