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 2 of 2

Thread: SQL Query Export Product Categories

  1. #1
    joecalardo is offline Member
    Join Date
    May 2011
    Posts
    61

    Default SQL Query Export Product Categories

    Hello, I am trying to figure out which of my products I have in my store that are not mapped, I feel like I have well over a few hundred that havn't been mapped over the months. I'd like a query to pull the products that are mapped, to show me what category they are in and I want to show the products that do not currently have a mapping as well. Thank you.

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

    Default

    Code:
    select p.ProductID, p.Name, pc.CategoryID, c.Name
    from Product as p (nolock)
    left join ProductCategory as pc (nolock) on p.ProductID=pc.ProductID
    left join Category as c (nolock) on pc.CategoryID=c.CategoryID
    order by 3,1
    The non-mapped ones will be at the top of the list with nulls for pc.categoryid and c.name.

    You'll probably want similar for the other entities, eg distributor, section, etc. if you use them
    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!