SELECT from the tree - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT from the tree

Author

Topic

kasper
Starting Member

7 Posts

Posted - 2006-12-14 : 16:59:59

Hi,
Can anybody suggest how to find all the children of the particular ancestor from the table where the family tree stored:
CREATE TABLE family (id int NOT NULL PRIMARY KEY, id_ancestor_m int NOT NULL, first_name varchar (80), second name varchar(80))?

Thanks.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-14 : 17:10:24

maybe i'm missing something, is this what you mean? i'm confused because it seems so simple...

select * from family where id_ancestor_m=<insert_ancestor_in_question_here>

http://www.elsasoft.org

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-14 : 17:32:52

I am guessing kasper is talking about going down an unlimited number of hierarchal levels. There are several posts and articles here about "trees and hierarchies". Here is some sample code I did some time ago: (you can just exec the entire code block to see the results)


set nocount on
declare @items		Table (itemid int identity(1,1), itemName varchar(10))
declare @association	Table (itemid int, parentitemid int)
declare @tree		Table (parentitemid int, itemid int, lev int, struct varchar(200))

declare @lev int
set @lev = 0

insert @items (itemname)
select 'item01' union
select 'item02' union
select 'item03' union
select 'item04' union
select 'item05' union
select 'item06' union
select 'item07' union
select 'item08' union
select 'item09' union
select 'item10' union
select 'item11' union
select 'item12'
order by 1

insert @association
select 1,null union 
select 2,1 union
select 3,1 union
select 4,3 union
select 5,4 union
select 6,2 union
select 7,2 union
select 8,6 union
select 9,6 union
select 10,2 union
select 11,10 union
select 12, 1

--get the root nodes
insert @tree
select null, a.itemid, @lev, itemname
from	@association a
JOIN	@items i ON i.itemid = a.itemid
where	parentitemid is NULL


--while children exist for current parent level
while @@Rowcount > 0
Begin
	set @lev = @lev + 1

	insert @tree
	select t.itemid, a.itemid, @lev, isNull(t.struct+'.','') + itemname
	from	@association a
	JOIN	@items i 
			ON i.itemid = a.itemid
	JOIN	@tree t 
			ON t.itemid = a.parentitemid
			and t.lev = @lev - 1

	--avoid circular references
	Left JOIN @tree excl
			ON excl.parentitemid = a.itemid
	where	excl.itemid is NULL
End

print 'helper table: @tree'
select * from @tree order by itemid

print '
One way to display the formatted tree'
select	replicate(char(9), lev) + 
	 convert(varchar,i.itemid) + '-' + 
	 itemname [struct]
From	@tree t
JOIN	@items i ON t.itemid = i.itemid
Order by i.itemid

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources