Post reply

Looping query Parent and child, and childs childs ......

  • October 22, 2004 at 3:10 am

    #60469

    Ive lost the plot this morning completely

    I havea single table which holds the ROW Ids,

    Im Trying to write a query which will return from

    one row Id ,

    all its children

    all of the childrens' childen

    All of the childrens childrens children

    and so on the structure could be as much as 7-8 deep

    but I don't want to particulary risk missing out on all the children to this

    has anyone come to the same problem and got the solution to hand

    must stop drinking on thursday night .. (Fridays are not weekends ...... YET!)

    ROW ID Parent ROW ID

    x1 | x2

    x2 | x6

    x3 |

    x4 | x7

    x5 | x7

    x6 |

    x7 | x2

    x8 | x2

    x9 | x1

    The XMan

  • October 22, 2004 at 5:15 am

    #527620

    The only way I can see you achieving this is by linking all the tables together using a left outer join.

    For example.

    create table tmp_parent

    (parentid int, name nvarchar(5))

    create table tmp_child1

    (child1id int,parentid int, name nvarchar(5))

    create table tmp_child2

    (child2id int,child1id int, name nvarchar(5))

    select p.name, c1.name, c2.name from tmp_parent p

    left outer join tmp_child1 c1

    on p.id = c1.parentid

    left outer join tmp_child2 c2

    on c1.id = c2.child1id

    .....and so on.

    This will ensure that the the parents and children will show down to the point that they finish.

    I cannot see a way to do this dynamically other than run exists queries, which will take a lifetime to run.

     

    ------------------------------
    The Users are always right - when I'm not wrong!

  • October 22, 2004 at 7:05 am

    #527636

    The only way I have achieved this is to use temp tables and a loop

    In what format do you want the output?

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 22, 2004 at 10:31 am

    #527692

    Thanks guys , hopefully i can adapt that

    my original Query Would only look at the top level firm and then the secondary level ,

    i haven't yet been able to work out the loops yet,

    I now envision the query as being first to return all the ROW IDS

    and then look across at the other tables to pick up the data that I need from these firms, nice especially since it travels across 6 tables (in siebel)

    John, when you said " and so on what did you mean" slightly affraid that I may have missed a vital bit there

  • October 25, 2004 at 2:58 am

    #527775

     By so on....

    I meant that you can add additional children tables further down the rows. Again all hard coded with joins to the previous child.

     

    i.e

    left outer join tmp_child3 c3

    on c2.id = c3.child2id

    ------------------------------
    The Users are always right - when I'm not wrong!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply