How to get Category Name in SQL for Product?

[Mike718NY]

Is there a way to join some tables to get the Category Name?
I was able to get the Manufacture name, but I can't see how to join the Category Table.

SELECT [ac_Products].[Name]
,[Price]
,[MSRP]
,ac_Manufacturers.Name as 'Manufacturer'
,[Sku]
,[ImageUrl]
,[Description]
,[VisibilityId]
FROM [super-v_store].[dbo].[ac_Products]
left JOIN ac_Manufacturers ON ac_Products.ManufacturerId = ac_Manufacturers.ManufacturerId

[jmestep]

You need to find the categoryId in the ac_CatalogNodes table then get the category name from ac_Categories. In ac_CatalogNodes, for the product, the catalognodeid would be the productid with a catalognodetypeid of 1. For example, if your product id is 40 in Able's test products, the categoryid in the ac_CatalogNodes table is 3

[david-ebt]

Try this query:

SELECT p.[Name]
,p.[Price]
,p.[MSRP]
,m.[Name] as 'Manufacturer'
,p.[Sku]
,p.[ImageUrl]
,p.[Description]
,p.[VisibilityId]
,c.[Name] as 'Category'
FROM [ac_Products] p
left JOIN ac_Manufacturers m ON p.ManufacturerId = m.ManufacturerId
left JOIN ac_CatalogNodes cn ON cn.CatalogNodeId = p.ProductId
left JOIN ac_Categories c on cn.CategoryId = c.CategoryId
where p.ProductId = 40

Note that if a product is in multiple categories you will get multiple rows for that product.

[Mike718NY]

thanks . .. I 'll give it try.