Rule 1: diplaying only those ancestors (from parent column) from table2 which has child, or grandchild or any other sub-level in table2 except the last level.
Sample (from table2): So "1" has child "2" and "2" has child "3" and "3" has child "4". So I would like to display data from parent column which is: "1", "2" and "3".
There is one more parent-child relationship in table2: parent "5" has child "6" and child "6" has child "7". So I would like to display data from parent column which is: "5" and "6".
Rule 2: I would like to display only those ancestor sub-level that has the matching row in table1.
Sample: There are two ancestors in table2: "1" and "5".
The final result
So combining rule1 and rule2 there the final result is: "1", "2", "3".
Question: how to write such SQL? Is it possible to write it?
The SQL statements to prepare sample:
create table table1 (parent integer)
insert into table1 values (1)
create table table2 (child integer, parent integer)
insert into table2 values (1,2)
insert into table2 values (2,3)
insert into table2 values (3,4)
insert into table2 values (5,6)
insert into table2 values (6,7)
If I got what you want correctly, you basically want all the descendants of the ancestors in table1. If this is so, you do not need a join (although a join is valid for using in recursion). I have not tried this out, but it would look something like this:
with t1 (parent, child) as (
select * from table2 where parent in (select parent from table1)
select * from table2, t1 where t2.parent = t1.child
) select * from t1
1. How to get only first column out of this SQL?
2. The result of your SQL is only part of the whole SQL I need to write (10 more tables involved and joined together with the result of your SQL) so how can I write:
select parent from (your_sql) from temp (temporaly table SQL)
It returs error:
SQL0104N An unexpected token "AS" was found following "TH T1 (PARENT,CHILD)".
Expected tokens may include: "JOIN". SQLSTATE=42601
it looks like if 'with' sintax is used it can not be processed in temporary table anymore.
Replying to a post that is about 3 years old and asking a vague question related to it will not get you much response. Please open a new thread, state your DB2 version and OS and explain what you want to do and what you tried, and maybe you will get some positive help.