Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 3 of 3

Thread: Helo with SQL Query

  1. #1
    harsha.gus is offline Senior Member
    Join Date
    Mar 2009
    Posts
    301

    Exclamation Helo with SQL Query

    Hi can any one help me with this query

    C#/VB.NET Code:
      select p.productidp.nameisnull(pv.name''VariantNamep.descriptionp.senamep.ImageFileNameOverridep.SKUisnull(p.FroogleDescription''ProductFroogleDescriptionp.SEKeywords,
                    
    p.ManufacturerPartNumberpv.priceisnull(pv.saleprice0salepriceisnull(pv.FroogleDescription''VariantFroogleDescriptionisnull(pv.description''VariantDescrpv.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 variantidsum(quaninventory from dbo.inventory group by variantidi on pv.variantid i.variantid
                where p
    .IsSystem=
                    
    and p.deleted 
                    
    and p.published 
                    
    and p.ExcludeFromPriceFeeds 
                    
    and pv.isdefault 

    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.
    rbgx
    AspDotNetStorefront ML
    v8.0.1.4

  2. #2
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    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
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!

  3. #3
    harsha.gus is offline Senior Member
    Join Date
    Mar 2009
    Posts
    301

    Exclamation thankyou

    Thank you very much it worked.
    rbgx
    AspDotNetStorefront ML
    v8.0.1.4