How to do this?

  • Hi everybody,

    I have a table which has a circular relationship.

    CategoryID

    int

    PK NOT NULL

    CategoryName

    varchar(50)

    NOT NULL

    ParentID

    int

    FK (CategoryID) NULL

    I also have a Items table which contains the details of item along with the categoryid which it belong to.

    My question is how can I get all Items of a category and all its sub categories as well. For example if chosen categoryid is of computers then hard-drives, components, CD-DVD drives, Monitors, Peripherals etc.

    Thank you in advance.

  • Join Category table to itself and point CategoryID = ParentID on the second table so that the second table will refer to the children of the current category.

    If you want to make this recursive (which I don't think you've asked for) then you'll have to use a UDF in SQL 2000.  There is no standard SQL Recurse function unfortunately, even though this is available in most other RDBMS.  If you do use a UDF be aware the maximum recurse limit (call stack) is 32 - so a depth level of 32 maximum.  If you wish to know how to do a recursive function, post back and I'll tell you.  Otherwise, just to get the children of current category...

    Select currentcategory.CategoryID, currentcategory.CategoryName, childitem.CategoryID, childitem.CategoryName
    from Categories currentcategory inner join Categories childitem
    on currentcategory.CategoryID = childitem.ParentID

    From above you will get a list of all children of each category.  If you add a where clause to specify a specific category, it will return just the children of that category.  You can obviously use the above query to join to your Items table (for both currentcategory and childitem) to get any additional details you need.

    Dave Hilditch.

  • Following code is pseudo code - I've typed it straight in here so no guarantees it will work straight off in Query Analyzer.

    create function getChildren(@categoryid int)

    returns @children table

    ( categoryid int )

    as

    begin

    -- insert categories for this level

    insert into @children

    select categoryid from categories where parentid = @categoryid

     

    --insert categories for sub-categories

    insert into @children

    select dbo.getChildren(categoryid)

    from categories where parentid = @categoryid

    end

     

    Hope this helps!  Remember, max 32 depth - sounds like that should be enough for you though.

    Good luck.

    Dave Hilditch.

     
     
  • Thanks to all of you.  Yours views helped me a lot.  Problem solved.

Viewing 4 posts - 1 through 3 (of 3 total)

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