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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-28 : 17:30:35
|
Sean writes "Given a term in a grandparent/parent/child relation, find the grandparent (or topTerm) and all parents/child below it which lead to the term. There can be upto 6 levels.
for example: ("doberman")
Result:
animals
"---wild animals
"---circus animals
"---domestic animals
"----"---- cats
"----"---- birds
"----"---- dogs
"----"------ "--german sheperd
"----"------ "--bull dog
"----"------ "--doberman
two tables:
tblTerms having two fields: termNum, term
tblRelations having two fields: BTptr, NTptr
BTptr is the termNum relating to the broader termNum NTptr.
NTptr is the termNum relating to the narrower termNum BTptr.
values:tblTerms
animal-----------1
wild animal------2
circus animal----3
domestic animals-4
cats-------------5
birds------------6
dogs-------------7
german sheperd---8
bull dog---------9
doberman---------10
values: tblRelations
BTptr NTptr
2----------1
3----------1
4----------1
5----------4
6----------4
7----------4
8----------7
9----------7
10----------7
" |
|
|
|
|
|
Recursive query & Alaises in heirachical structure