>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
