I am trying to solve the exact same issue as the person who created this question at stackOverflow regarding hierarchical tables (ie. self referencing tables). The examples below are from that article. So, if 'D' is the given element then the Ids that should be returned are 1,2,3,4,5,6 because they are all related.
I need to return the entire tree (ie. list of elements) to which the single element given belongs
The solution given uses Oracle sql, and I am having trouble converting it to iSeries sql since some of the keywords are Oracle specific
Any help is greatly appreciated
TIA
Walt
I need to return the entire tree (ie. list of elements) to which the single element given belongs
The solution given uses Oracle sql, and I am having trouble converting it to iSeries sql since some of the keywords are Oracle specific
Any help is greatly appreciated
Code:
ID Parent_ID Name 1 (null) A 2 1 B 3 1 C 4 2 D 5 3 E 6 5 F 7 (null) G 8 (null) H
Code:
with t as ( select id from your_table where name = 'D' -- your starting point ) select id from ( select id, parent_id from your_table where parent_id is not null union all select parent_id, id from your_table where parent_id is not null union all select id, null from t ) start with parent_id is null connect by nocycle prior id = parent_id
Walt
Comment