Hi,
I have one SQL problem, that I am not capable of solving. Any help is very appreciated. Below is an example of the problem.

Table1:
Table2:
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".
Result: "1"

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:
Thanks a lot,
Grofaty

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)
union all
select * from table2, t1 where t2.parent = t1.child
) select * from t1

HTH

Andy

ARWinner,
That is correct.

Your suggestion returs error:
But I see there must be some other syntax errors in SQL for example: "select * from table2, t1 ", because t1 does not exist.

Any suggestions are very appreciated.
Thanks,
Grofaty

Sorry for the delay, I was away on vacation. The only thing I can see wrong was i forgot the correlation from table2 to t2:

with t1 (parent, child) as (
select * from table2 where parent in (select parent from table1)
union all
select * from table2 as t2, t1 where t2.parent = t1.child
) select * from t1

t1 is the tablename of the common table expression of this recursive query.

Andy

go thru. following articles regarding recursive sql :

http://www-128.ibm.com/developerwork...steinbach.html

http://www-128.ibm.com/developerwork...lau/index.html

Rahul Singh

It's been a long time since I wrote my last recursive SQL script.

Perhaps http://www.dbforums.com/db2/1076604-how-pivot-dates-into-columns-printthread.html or to a lesser exted http://www.dbforums.com/archive/inde...t-1119101.html may help.

The SQL returs error:
As I see the first SQL is probably also not good:
"select * from table2 where parent in (select parent from table1)" it should be
"select * from table2 where child in (select parent from table1)".

Hi,
the closes SQL I can write is (producing more records is better then less):
but this SQL produces result:
The 6 is incorrect! So the right answer should be:
Any idea how to eliminate number 6?

This should work (I tested it):

with t1 (parent, child) as (
select * from table2 where parent in (select parent from table1)
union all
select t2.parent,t2.child from table2 as t2, t1 where t2.parent = t1.child
) select * from t1

Andy

ARwinner,
my table2 is (parent int, child int)
your table2 is (child int, parent int)

so I have changed your SQL and final SQL looks like:
it looks like it is working...
Thanks a lot,
Grofaty

Arwinner,
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:
it looks like if 'with' sintax is used it can not be processed in temporary table anymore.
Thanks,
Grofaty

To get only the first column back, just change

with t1 (parent,child) as (select * from table2 where child in
(select parent from andy.tab1) union all select t2.child,t2.parent
from table2 as t2, t1 where t2.child = t1.child)
select * from t1

to

with t1 (parent,child) as (select * from table2 where child in
(select parent from andy.tab1) union all select t2.child,t2.parent
from table2 as t2, t1 where t2.child = t1.child)
select parent from t1

All that changes is the last line. To retrieve fom other tables, just join to this select statement.

Andy

ARWinner,
thank you very much. You helped med in a huge scale...
Grofaty

How about from Child travel back to all Parents?

I'm able to do this by reversing the logic of the query, but it just return me 2 sets of same records, please help.

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.

Andy