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 - 2000-12-28 : 17:56:03
|
Jason writes "There is an effective method for querying tree structures in Oracle (wish they had this in SQL Server)...here's an excerpt from an Oracle SQL*Plus Intro/Tutorial, http://www.pef.czu.cz/pef/kii/merunka/documents/Databases/sqlplus.html
Tree Queries
Another form of recursive query is the tree query. A tree query decomposes the table such that each row is a node the tree and nodes are related in levels. Consider the Students table defined above.
Bill tutors Alex, Mary and Sue.
Mary tutors Liz and Ed
Sue tutors Petra
Using the SQL SELECT statements CONNECT BY and START WITH clauses, we can form a set of relationships between the rows of the table that form a tree structure.
START WITH - indicates which row the tree should start with.
CONNECT BY - indicates how successive related rows are to be identified and included in the result.
LEVEL - a pseudo-column that indicates which level of the tree the current row is assigned to.
The following example prints a tree structure modeled after the tutoring relationships in the Students table.
SELECT LPAD(' ',2*(LEVEL-1)) || students.name
As TutorTree
FROM students
START WITH studentid = 'S101'
CONNECT BY PRIOR studentid = student_tutorid;
TUTORTREE
---------------------------------
Bill
Alex
Mary
Liz
Ed
Sue
Petra
7 rows selected." |
|
|
|
|
|
Recursive Joins, Tree Structures