Thread: Helo with SQL Query

Hi can any one help me with this query

C#/VB.NET Code:

  select p.productid, p.name, isnull(pv.name, '') VariantName, p.description, p.sename, p.ImageFileNameOverride, p.SKU, isnull(p.FroogleDescription, '') ProductFroogleDescription, p.SEKeywords,
                p.ManufacturerPartNumber, pv.price, isnull(pv.saleprice, 0) saleprice, isnull(pv.FroogleDescription, '') VariantFroogleDescription, isnull(pv.description, '') VariantDescr, pv.condition,
                ROW_NUMBER() OVER (ORDER BY p.productid) AS ROW
            from dbo.product p 
                join dbo.productvariant pv on p.productid = pv.productid
                left join (select variantid, sum(quan) inventory from dbo.inventory group by variantid) i on pv.variantid = i.variantid
            where p.IsSystem=0 
                and p.deleted = 0 
                and p.published = 1 
                and p.ExcludeFromPriceFeeds = 0 
                and pv.isdefault = 1 


this query works fine for displaying the content i need.

I would like to retrieve the category Name for each product with this query, can anyone help me to do this.

either Immediate Category name or Root Category name anything is fine.

thank you very much for your time.

Code:

select c.name, p.productid, p.name, isnull(pv.name, '') VariantName, p.description, p.sename, p.ImageFileNameOverride, p.SKU, isnull(p.FroogleDescription, '') ProductFroogleDescription, p.SEKeywords,
                p.ManufacturerPartNumber, pv.price, isnull(pv.saleprice, 0) saleprice, isnull(pv.FroogleDescription, '') VariantFroogleDescription, isnull(pv.description, '') VariantDescr, pv.Condition,
                ROW_NUMBER() OVER (ORDER BY p.productid) AS ROW
            from dbo.product p 
                join dbo.productvariant pv on p.productid = pv.productid
                left join (select variantid, sum(quan) inventory from dbo.inventory group by variantid) i on pv.variantid = i.variantid
                join ProductCategory pc on pc.ProductID = p.ProductID
                join Category c on pc.CategoryID = c.CategoryID
            where p.IsSystem=0 
                and p.deleted = 0 
                and p.published = 1 
                and p.ExcludeFromPriceFeeds = 0 
                and pv.isdefault = 1

Thank you very much it worked.