Unknown tree structure table design??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Unknown tree structure table design???

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-12-11 : 02:16:40

hello friends!
Can any body guide me regarding Tree/hierarchy Multi-Referential table structure.

What i have is only for known tree structure i.e upto 3 or 4 level but if i waanna to find nth level hoe shud i design my table structure.

T.I.A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 02:46:17

The most simple and straightforward would be

ID INT (NOT NULL) <-> Name VARCHAR(100) (NOT NULL) <-> ParentID (NULL)

Peter Larsson
Helsingborg, Sweden

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-12-11 : 06:40:18

Hello Like this??

create table emp (id int,Name varchar(2),BelongTo int)

insert into emp (id,Name,BelongTo) values(1,'A',NULL)
insert into emp (id,Name,BelongTo) values(2,'B',1)
insert into emp (id,Name,BelongTo) values(3,'C',2)
insert into emp (id,Name,BelongTo) values(4,'D',2)
insert into emp (id,Name,BelongTo) values(5,'E',3)
insert into emp (id,Name,BelongTo) values(6,'F',3)
insert into emp (id,Name,BelongTo) values(7,'G',4)
insert into emp (id,Name,BelongTo) values(8,'H',5)
insert into emp (id,Name,BelongTo) values(9,'I',5)

AND QUERY LIKE???

select Distinct A.ID,A.Name,B.Name BTo,C.name BToBTo,d.name BToBToBTo,e.name BToBToBToBTo
from emp A
left join emp B on b.id = A.BelongTo
left join emp c on B.BelongTo = C.id
left join emp d on c.BelongTo = d.id
left join emp e on d.BelongTo = e.id

BUT IF unknow level is there then query will be unknown level????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 06:49:58

Try this http://weblogs.sqlteam.com/brettk/archive/2006/11/13/23995.aspx

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources