Self Join

  •  

    Hi,

    I have a table that contains categoryid, categoryname and basecategoryid.

    For every category, the parent categoryid is stored in the basecategoryid field.

    Each category can have many levels of sub categories.

    I want a query which will retrieve all the sub and sub sub categories( till the last level), given a categoryid.

    Appreciate help in this regard.

    regards,

    Deepika.

  • What have you tried? 

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I am able to drill down only to one level.

    I used the following query:

    SELECT c1.categoryid as ParentCategoryID, c2.categoryid as SubCategoryID, c1.categoryname AS ParentCategory, c2.categoryname AS SubCatgeory

    FROM category AS c1 INNER JOIN category AS c2 ON c1.categoryid=c2.basecategoryid

    ORDER BY c1.categoryid;

     

     

  • you will need helper objects (#temptb) to solve this tree in sqlserver2000.

    With sql2005 you would use a CTE.

    Search SSC for "tree" and you'll find some options.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Another place to look is under "Expanding Hierarchies" in Books OnLine...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • <>

    Hi, have you try something called a cartesian operation (sorry im French and i m not sure it is the good translation), actually the aim is to make all the couple possibilities and then restrict with the where clause in order to check if the original record got a subcategory, ive made a query like this but ive got hole in my brain, i will try to find it and then i will post it.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply