February 19, 2007 at 5:15 am
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.
February 22, 2007 at 8:00 am
This was removed by the editor as SPAM
February 23, 2007 at 9:24 am
This is my first ever post and I hope there will be something helpful here. I used this technique to return hierarcical personnel information. I'm using table variables in the example here, but in my database (I'm not a DBA, I'm a .NET developer), I found performance was actually better with a temp table. Be sure to try both ways.
-- Create a table to mimic user's question
declare @Temp table
(CategoryID int,
CategoryName varchar(20),
BaseCategoryID int)
insert
into @Temp values (1, 'Admin', 0)
insert into @Temp values (2, 'User Maint', 1)
insert into @Temp values (3, 'Reports', 0)
insert into @Temp values (4, 'Report #1', 3)
insert into @Temp values (5, 'Report #1.a', 4)
insert into @Temp values (6, 'Report #1.b', 4)
insert into @Temp values (7, 'Report #2', 3)
insert into @Temp values (8, 'Report #2.a', 7)
insert into @Temp values (9, 'Report #3', 3)
insert into @Temp values (10, 'Report #3.a', 9)
insert into @Temp values (11, 'Report #3.b', 9)
insert into @Temp values (12, 'Report #3.c', 9)
-- Now get data for a particular CategoryID
declare @CategoryID int
set @CategoryID = 3
-- Create table to hold results that will be returned
declare @Results table
(CategoryID int,
CategoryName varchar(20))
-- Insert the parent row in first
insert into @Results
select CategoryID, CategoryName
from @Temp
where CategoryID = @CategoryID
-- Get all downstream children
while @@rowcount > 0
begin
insert into @Results
select t.CategoryID, t.CategoryName
from @Temp t
inner join @Results r on t.BaseCategoryID = r.CategoryID
and not exists (select * from @Results r
where t.CategoryID = r.CategoryID)
end
select * from @Results
I've not taken into account any sort order or anything. You may want to consider adding a column for that.
Good Luck
February 23, 2007 at 10:17 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply