469,950 Members | 2,334 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Need SQL query

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


Jul 19 '05 #1
1 1565
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)
--
- Vishal

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by lawrence | last post: by
9 posts views Thread by netpurpose | last post: by
10 posts views Thread by L. R. Du Broff | last post: by
3 posts views Thread by pbd22 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.