SEO

#1 (**permalink**) 02-29-2008, 04:42 AM

Hi,

I have a tree structure which is maintained through the use of a path
enumerated column:

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));

or maybe:

SELECT id, path FROM items WHERE PATH_EQUALS(id, path));

or maybe something else altogether. This should return:

ITEM_ID PATH
------- -------
1 0
2 0:1
3 0:1:2
4 0:1:2:3

#2 (**permalink**) 02-29-2008, 04:42 AM

You're going to have to parse the path in order to use the nodes as keys;
here is an anchor to a function that can do it (you'll have to modify it to
use colons; it's set up for commas). I recommend that you read the rest of
the article as well...

http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

"Ones Self" <nutgg001@sneakemail.com> wrote in message
news:a2122d77.0410291402.17ebaca3@posting.google.c om...
> Hi,
>
> I have a tree structure which is maintained through the use of a path
> enumerated column:
>
> 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));
>
> or maybe:
>
> SELECT id, path FROM items WHERE PATH_EQUALS(id, path));
>
> or maybe something else altogether. This should return:
>
> ITEM_ID PATH
> ------- -------
> 1 0
> 2 0:1
> 3 0:1:2
> 4 0:1:2:3

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Selecting All Parents in a Path Enumerated Tree