Looksl like you're wanting an equivalent to oracle's CONNECT BY syntax. There's no direct equivalent, unfortunately. If you want an alternate way to construct trees, which will work on ANY decent SQL DBMS (mysql, postgres, etc...) check out the NESTED SET HIERARCHY posed by Joe Celko. Search for "nested set hierarchy" at your favorite usenet archive.
Or, if you're stuck with that schema structure,
you might try the "expand" procedure, as outlined at microsoft's web site:
Knowledgebase article: 248915
http://support.microsoft.c
If you need further help adapting that example to your table (looks like a search-and-replace to me) let me know.
Main Topics
Browse All Topics
by: xxg4813Posted on 2003-04-09 at 15:20:20ID: 8303269
Hi,
All right, try this:
1.
create table tblid (uid int, value int, parent_uid int)
insert into tblid values(1, 101,null)
insert into tblid values(2, 102, 1)
insert into tblid values(3, 103, 2)
2.
drop proc getParent
go
create proc getParent @uid int
as
declare @tblid table(uid int, value int, parent_uid int, level int)
declare @level int
set @level = 1
insert into @tblid(uid, value, parent_uid, level)
select uid, value, parent_uid, 1
from tblid
where uid = @uid
while @@rowcount <> 0
begin
set @level = @level+1
insert @tblid(uid, value, parent_uid, level)
select t.uid, t.value, t.parent_uid, @level
from tblid t, @tblid s
where t.uid = s.parent_uid and s.level = @level-1
end
select * from @tblid
3.
exec getParent 3
Good luck!