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