Need SQL query

[Bola]

Hi there. U have table structure shown here:

category (Int - AutoIncrement) parent_category(Int) Title(string)
1 0
HOME PAGE
2 1
FIRST DEPTH 1
3 1
FIRST DEPTH 2
4 1
FIRST DEPTH 3
5 2
SECOND DEPTH 1
6 3
SECOND DEPTH 2
7 3
SECOND DEPTH 3
8 6
THIRD DEPTH 1

I want 1 (one) SQL query that can return me full depth for certain caregory.
In some meta-language
for category 8 it look like this:


SELECT * FROM table WHERE category = 8
WHILE parent_category <> 1
SELECT * FROM table WHERE category = [parent_category from previous
iteration]
WEND


So in that case resulting recordset would be

category (Int - AutoIncrement) parent_category(Int) Title(string)
8 6
HOME PAGE
6 3
FIRST DEPTH 1
3 1
FIRST DEPTH 2

I know that I can do this using more that one recordset, but I want all this
in one recordset.
I am using ADO, not ADO.NET

Thanks

 

[Vishal Parkar]

Bola,

You can achieve this using user defined function, see following example.

create table tree
(category Int, parent_category Int, Title varchar(50))
go
insert into tree
select 1, 0,'HOME PAGE' union all
select 2, 1,'FIRST DEPTH 1' union all
select 3, 1,'FIRST DEPTH 2' union all
select 4, 1,'FIRST DEPTH 3' union all
select 5, 2,'SECOND DEPTH 1' union all
select 6, 3,'SECOND DEPTH 2' union all
select 7, 3,'SECOND DEPTH 3' union all
select 8, 6,'THIRD DEPTH 1'
select * from tree
go

create function fn_get_tree (@y int)
returns
@tb table(empid int,
supervisor int,
empname varchar(300)
)
as
begin
declare @x table (empid int)

insert into @x
select parent_category from tree where category = @y
union all
select @y

while 1=1
begin
insert into @x
select parent_category from tree where category in (select distinct empid from @x)
and parent_category not in(Select empid from @x)
if @@rowcount = 0
break
end

insert into @tb
select * from tree where
exists
(select * from @x a where a.empid= tree.category)

return
end
go

--usage
select * from fn_get_tree (7)