complex self join

[btt423]

Here is the situation:

I have a property table with various information. This table has a prop_id that identifies each individual property. The table also has a field named prop_orig_id that is a foreign key to the prop_id field of the same table. Therefore, it is a unary relationship.

A property can be a child of another property. And a child can have children.

Example:

PROP_ID | PROP_DSC | PROP_ORIG_ID
----------------------------------------
10 | Property 10 |
11 | Property 11 | 10
12 | Property 12 | 11
13 | Property 13 |
14 | Property 14 | 12
15 | Property 15 |
----------------------------------------
I need to be able to select all the property records for property 10 including it's child records and their child records and so forth.

My result set should be (if wanted prop_id and prop_dsc):

--------------------
10 |
11 | Property 11
12 | Property 12
14 | Property 14
--------------------

I am using DB2 7. Is this possible? Currently it is being done with a recursive function calling the db over and over again.

Thanks, Brinson

 

[swampBoogie]

DB2, as one of a chosen few, supports recursive queries as defined in SQL 99. Something like

with rec(prop_id,prop_dsc,prop_orig_id)as
(select st.prop_id,st.prop_desc,st.prop_orig_id
from proptable as st
where prop_id = 10
union all
select child.prop_id,child.prop_desc,child.prop_orig_id
from rec as parent, proptable as child
where child.prop_orig_id = parent.prop_id )
select distint prop_id,prop_dsc,prop_orig_id
from rec
order by prop_id,prop_orig_id

Rather complicated code for something that should be fairly easy.

This is totally untested.

 

[r937]

db2 supports recursive queries? whoa [makes quick note]

i thought only oracle

thanks mister or miss or ms boogie, as the case may be


rudy

 

[btt423]

swampBoogie,

Will this work on DB2 for the mainframe? I was able to get it to successfully work on a test database I have on a Win 2000 box, but I keep getting an error with a begin parenthesis "(" when run on the mainframe (which will is where it will be used).

Thanks for your help, Brinson

 

[swampBoogie]

Will this work on DB2 for the mainframe?

I really can't say in which versions this is supported or not but maybe you could get a more definitive answer in the DB2 forum.