how to get the complete list of child tables and their child tables and so on...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

amitabh.mehra@gmail.com - 30 May 2007 12:57 GMT

Suppose I have the following scheam (parent->child) :
A->B->C->D, E->F

Can someone help me with the sql query in db2 which will give me the
complete chain once i give it the parent table. eg. for input A, i
should get B, C, D and for input E it should give me F.

Thanks

Reply to this Message

Serge Rielau - 30 May 2007 13:32 GMT

> Suppose I have the following scheam (parent->child) :
> A->B->C->D, E->F
>
> Can someone help me with the sql query in db2 which will give me the
> complete chain once i give it the parent table. eg. for input A, i
> should get B, C, D and for input E it should give me F.

You need to write a recursive query over SYSCAT.REFERENCES.

Enjoy
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

amitabh.mehra@gmail.com - 30 May 2007 14:14 GMT

> amitabh.me...@gmail.com wrote:
> > Suppose I have the following scheam (parent->child) :
[quoted text clipped - 13 lines]
> DB2 Solutions Development
> IBM Toronto Lab

Can someone help me formulate this query?

Reply to this Message

Serge Rielau - 30 May 2007 14:29 GMT

>> amitabh.me...@gmail.com wrote:
>>> Suppose I have the following scheam (parent->child) :
[quoted text clipped - 4 lines]
>> You need to write a recursive query over SYSCAT.REFERENCES.
> Can someone help me formulate this query?

Download my "SQL on Fire" talk from here:
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html
It explains recursion and has an example.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message