Help with loop???

[javoine]

I have a table that looks like this:

create table rows (
row_key integer primary key,
name varchar(40),
parent integer references rows(row_key)
);


It stores a tree structure, so that this data:


row_key | name | parent
------------------------------------
1 | Level 1 | null
2 | Level 2 - Left | 1
3 | Level 2 - Right | 1
4 | Level 3 - Left | 2
5 | Level 3 - Mid. | 2
6 | Level 3 - Mid. | 3
7 | Level 3 - Right | 3
8 | Level 4 - Left | 4
9 | Level 4 - Mid. | 4
10 | Level 5 - Left | 8
11 | Level 5 - Mid. | 8

means that row_key 1 is the root of the tree, row_keys
2 and 3 are its children, row_keys 4 and 5 are children
of row_key 2, etc.

How do I write a select that will return the
ancestors of a row given the row_key of the row?
In other words, given row_key 10, I want to select
so that I get:

row_key | name | parent
------------------------------------
1 | Level 1 | null
2 | Level 2 - Left | 1
4 | Level 3 - Left | 2
8 | Level 4 - Left | 4

any suggestions? examples?

 

[vongrunt]

What you made is called "adjacency list model" (ALM). It provides only minimal necessary information (maybe you also need "tab index" value that indicates order of siblings). ALM is not good because common requests like:

- gimme all childs for node X
- show me path to node X

... require row-based logic. In cases when max. depth is fixed you can use self-joins. Other than that, there are several ways to implement tree structures in SQL. IMO the most known are: materialized paths, nested sets and nested intervals. This link may help: faq183-5322; ask is anything isn't clear enough.

 

[javoine]

I'm not sure I can use any of that...I was kind of looking for an example. This is what I'm hoping to do:

At first glance it looks like the most efficient way would be to build a
cursor that would loop through the records selecting the "row_key" for the
"parent" of a submitted row_key and loop through until the parent is null.

for instance this simple statement
select * from rows where row_key = (select parent from rows where row_key =
'10')

retrieves the row for the specified key, but then looks no further.
Producuing

ROW_KEY NAME PARENT
8 Level 4 - Left 4

what I want it to do is now look at parent 4 and retrieve that row numbers information.

any suggestions? if this other area you directed me to meets this criteria then perhaps I am reading it wrong and will look agian.

Thank you in advance!

 

[vongrunt]

No cursor... loop. Here is pseudocode:

set initial @node
create temp table
while @node is not null
begin
	get data for that @node
	insert data into temp table
	set @node = parent node
end
select * from temp table

It is the simplest to understand, but not the best performance-wise.

 

[javoine]

thank you for all your help. I ended up performing the following

select d1.*
from rows d1, rows d2
where d1.row_key = d2.row_key
and d1.row_key in (select d2.row_key from rows d2
start with d2.row_key in (&&enternum)
connect by prior d2.parent = d2.row_key)
and d1.row_key < &&enternum

you really got me searching though and I appreciate the different options available. Thanks so much!

 

[vongrunt]

Just for the record: haven't ya missed a forum? START WITH/CONNECT BY PRIOR are Oracle extensions for SELECT statement...

 

[javoine]

Ya, posted in the wrong area...sorry.