Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to write recursive SQL with join? I am not sure it is even posible.

    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:
    Code:
    parent
    -----------
              1
    Table2:
    Code:
    parent      child
    ----------- -----------
              1           2
              2           3
              3           4
              5           6
              6           7
    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:
    Code:
    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)
    Thanks a lot,
    Grofaty

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    ARWinner,
    Quote Originally Posted by ARWinner
    If I got what you want correctly, you basically want all the descendants of the ancestors in table1
    That is correct.

    Your suggestion returs error:
    Code:
    SQL0206N  "T2.PARENT" is not valid in the context where it is used.
    SQLSTATE=42703
    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
    Last edited by grofaty; 11-04-06 at 04:44.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It's been a long time since I wrote my last recursive SQL script.

    Perhaps http://www.dbforums.com/printthread.php?t=1076604 or to a lesser exted http://www.dbforums.com/archive/inde...t-1119101.html may help.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by ARWinner
    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
    The SQL returs error:
    SQL0421N The operands of a set operator or a VALUES clause do not have the
    same number of columns. SQLSTATE=42826
    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)".
    Last edited by grofaty; 11-21-06 at 07:30.

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    the closes SQL I can write is (producing more records is better then less):
    select child from table2 where child in (select parent from table1)
    union all
    select b.child from table2 a, table2 b where a.parent=b.child
    but this SQL produces result:
    CHILD
    -----------
    1
    2
    3
    6
    The 6 is incorrect! So the right answer should be:
    CHILD
    -----------
    1
    2
    3
    Any idea how to eliminate number 6?

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    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:
    Code:
    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
    it looks like it is working...
    Thanks a lot,
    Grofaty
    Last edited by grofaty; 11-24-06 at 04:33.

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    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:
    Code:
    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.
    Thanks,
    Grofaty

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    ARWinner,
    thank you very much. You helped med in a huge scale...
    Grofaty

  14. #14
    Join Date
    Oct 2009
    Posts
    1

    Smile How about from Child travel back to all Parents?

    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.

  15. #15
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •