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 |
|
label
Posting Yak Master
197 Posts |
 Posted - 2004-02-02 : 09:01:23
|
| I have a need to recursively drill down in through a database until I hit the bottom of that heirarchy. I will essentially be utilizing 2 tables to accomplish this task. Table 1: Prodtree_Element - contains details about each element. Primary Key: Prodtree_Element_IDTable 2: Prodtree_Link - defines relationships between products found in the Prodtree_Element table by having a Parent_ID column and a Child_ID column. Both ID's of course reference the primary key (Prodtree_Element_ID) from the above table. My Problem:Basically I need to figure out how to best return all of the lowest level ID's when given a single parentID. That parentID could reside at the top or 8 levels down...it will change with every selection. - You know you're at the bottom when the field "Prodtree_Element_Domain_Type"='Prodgraph' in the Prodtree_Element table. Any ideas how to recursively drill down a dynamic number of fields and ultimately return all of the Prodtree_Element_ID's from the lowest level? Thanks. |
|
|
HendersonToo
Starting Member
20 Posts |
Posted - 2004-02-02 : 12:08:20
|
| This is difficult to accomplish without adding either one or two columns to your table. There are two possible solutions, and I assume you have other code which depends on the parent_id and child_id, so we'll throw out trying to implement a binary tree with left and right integer sequences. But you should explore this method, see a write-up by Joe Celko at http://www.dbmsmag.com/9603d06.htmlYou'll need to add a depth column to your table, and write a trigger that can fire to keep this up-to-date. As far as I know, there is no solution that yields any sort of efficiency to this problem, without either using a Depth column design, or the Left/Right "pointer" design. Each design has a different set of performance goals, depending on whether you are querying records, or whether you are writing records.For your design, there is an Oreilly book which covers this pretty well, in the Transact-SQL Cookbook By Ales Spetic, Jonathan Gennick March 2002 ISBN: 1-56592-756-7ORA.com notes that the code samples can be downloaded: http://examples.oreilly.com/transqlcook/ |
 |
|
|
|
|
|
|
|
Recursive Search