>I have a table in the database which represents a tree like the one below.
>Each tree-node is a record in the table and has a primary-key.
>
> 1 1
> 2 1.1Green
> 3 1.1.1
> 4 1.1.2Green
> 5 1.2
> 6 1.2.1
> 7 1.2.2Green
> 8 2
> 9 2.2
>10 2.2.1
>11 3
>12 3.1Green
>13 3.1.1
>
>Now I have a dataset which suspends only the green tree-nodes.
>
>I need a SQL which suspends the complete tree-path for each green cell
>
>How can this be done???
>
>TIA,
>
>Sepp

With this table:

CREATE TABLE SeppTABLE
(
ID INTEGER NOT NULL,
PARENT_ID INTEGER,
GREEN VARCHAR( 3) CHARACTER SET ISO8859_1
COLLATE NO_NO,
CONSTRAINT PK_JOSEFTABLE PRIMARY KEY (ID)
);

and these values:

ID PARENT_ID GREEN
1 "No"
2 1 "Yes"
3 2 "No"
4 2 "Yes"
5 1 "No"
6 5 "No"
7 5 "Yes"
8 "No"
9 8 "No"
10 9 "No"
11 "No"
12 11 "Yes"
13 12 "No"

You get the result you want with this statement:

with recursive cte as
(select id, parent_id, green
from SeppTable
where Green = 'Yes'
union all
select id, parent_id, green
from SeppTable JT
join cte on JT.ID = cte.parent_id
)

select distinct id, green
from cte
order by id

HTH,
Set