Selecting All Parents in a Path Enumerated Tree - 10-29-2004 , 05:01 PM
I have a tree structure which is maintained through the use of a path
CREATE TABLE items (
item_id NUMERIC NOT NULL,
path VARCHAR2(64) NOT NULL
The path is is a colon separated list of ids of the nodes of the tree.
So, for example, in this structure:
0 -> 1 -> 2 -> 3 -> 4
item id 4 would have a path of '0:1:2:3' (0 is the root of all
items, and does not actually exist). Notice that the path does not
include the item's own id.
I would like to select all of the items in a given item's path:
SELECT id, path FROM items WHERE id IN (PATH_TO_IDS(path));
SELECT id, path FROM items WHERE PATH_EQUALS(id, path));
or maybe something else altogether. This should return:
Re: Selecting All Parents in a Path Enumerated Tree - 10-30-2004 , 09:42 AM
"Ones Self" <nutgg001 (AT) sneakemail (DOT) com> a écrit dans le message de
news:a2122d77.0410291401.6891b9f (AT) posting (DOT) google.com...
select id, path
from items, (select path basePath from items where id=&baseId)
where basePath like '%:'||id||':%' or basePath like id||':%' or basePath like '%:'||id
union all /* if you also want the BaseId full path */
select id, path where id = &baseId
order by 2