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

Thread: Category Path Query

  1. #1
    HomeIndoor is offline Member
    Join Date
    Nov 2008
    Posts
    64

    Wink Category Path Query

    I am looking for a simply query that will generate the category path as it should be uploaded via excel or wsi

    10 minute job for all you SQL guru's out there.

    I want to generate all of the paths for all of the categories that we currently offer.
    Thanks in advance.

    I can pay for your services.

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

    Default

    You probably want something like this:

    Code:
    WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
    (
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    WHERE ParentCategoryID = 0
    AND Deleted=0
    AND Published=1
    UNION ALL
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
    WHERE Deleted=0
    AND Published=1
    )
    
    SELECT ParentCategoryID, CategoryID, Name, ComputedLevel, Sort
    FROM Categories
    ORDER BY 5
    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
    HomeIndoor is offline Member
    Join Date
    Nov 2008
    Posts
    64

    Talking You are the man!!! --thank you

    WOW, I woke up this morning exhausted from having to rebuild our database and server, only sleeping 3 hours every night since our server crashed. I made the wife some breakfast, got me some coffee and opened my email and first thing I see is your response. I looked over your query and made this oh, this is well written and then ran your query and all I can say is wow, you are the man .

    I have been trying to combat this problem with the category paths for several days now, since I only have the category id and category name which is over 500 categories. I was hoping I could use the category id when updating via wsi but turns out I could not, but you have made my day.

    Forget the coffee, You just woke me up.

    Thank you very much. I am glad that their are still good people out there.
    If you need anything, let me know.

  4. #4
    xjeddan143 is offline Junior Member
    Join Date
    Nov 2010
    Posts
    1

    Default Thank you

    Thank you for the code. I really need this query for my System that I developing now.

    Thank you

  5. #5
    Cano is offline Member
    Join Date
    Sep 2005
    Location
    Wellington, FL
    Posts
    64

    Default

    wasn't looking for this, but it will come in handy later

    Thanks

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

    Exclamation nested Categories list

    Quote Originally Posted by esedirect View Post
    You probably want something like this:

    Code:
    WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
    (
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    WHERE ParentCategoryID = 0
    AND Deleted=0
    AND Published=1
    UNION ALL
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
    WHERE Deleted=0
    AND Published=1
    )
    
    SELECT ParentCategoryID, CategoryID, Name, ComputedLevel, Sort
    FROM Categories
    ORDER BY 5
    is it possible to get all records as in this query with a condition where ParentCategoryID='3' (it should go through a loop)
    let say if i have two category under categoryID 3 and those two categories have 4 subcategories under it (each). then the query should bring up total 10 (1+4+4) records not two

    thanks
    rbgx
    AspDotNetStorefront ML
    v8.0.1.4

  7. #7
    BFG 9000 is offline Senior Member
    Join Date
    Oct 2006
    Location
    South UK
    Posts
    882

    Default

    Just change :-

    WHERE ParentCategoryID = 0

    to

    WHERE ParentCategoryID = 3



    TTFN

    BFG

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

    Exclamation Thansk

    Quote Originally Posted by BFG 9000 View Post
    Just change :-

    WHERE ParentCategoryID = 0

    to

    WHERE ParentCategoryID = 3



    TTFN


    BFG
    i was dumb, i didn't even pay attention to the code.
    i am trying to join Product and ProductVariant Tables to the above query and retries all the products under a category.
    can you please help me joining Product and ProductVariant Tables to the above query.

    I really appreciate your help.

    thanks
    rbgx
    AspDotNetStorefront ML
    v8.0.1.4

  9. #9
    BFG 9000 is offline Senior Member
    Join Date
    Oct 2006
    Location
    South UK
    Posts
    882

    Default

    What's the exact output you want?

    Is it just a list of products that are in CategoryID 4 or any of its subcategories?


    TTFN

    BFG

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

    Exclamation Product List with Category Mapping

    Quote Originally Posted by BFG 9000 View Post
    What's the exact output you want?

    Is it just a list of products that are in CategoryID 4 or any of its subcategories?


    TTFN

    BFG
    I want all the product list under a main category and its subcategories with the Category Map, I have used your code ans made a temp table and got what i am looking for. But i am not sure if that is an efficient way. I am attaching that sql file.
    Hope you can suggest me any efficient way to achieve it.
    please use this link to download the text file containing the query
    http://www.sendspace.com/file/5d6gm5


    Thanks
    rbgx
    AspDotNetStorefront ML
    v8.0.1.4

  11. #11
    BFG 9000 is offline Senior Member
    Join Date
    Oct 2006
    Location
    South UK
    Posts
    882

    Default

    If you've already got what you wanted - then does it matter if it wasn't the most efficient way?


    TTFN

    BFG