# Thread: Recursive Queries -- any ideas on how to solve this interesting problem?

1. Registered User
Join Date
Dec 2002
Posts
123

## Unanswered: Recursive Queries -- any ideas on how to solve this interesting problem?

Hi guys,

Here's the question -- I have a table that has this structure/data :

ENTITYID ENTITYNAME TYPE PARENTID
----------- -------------------------------- ------ -----------
100 ESP 1 0
101 Account 2 100
102 Site 3 101
103 Meter1 5 102
104 Meter2 5 102
200 ESP2 1 0
201 Account2 2 200
202 Site2 3 201
203 Meter21 5 202
204 Meter22 5 202

There are two separate sets of data in this table that are all linked by parentid. Anything at the highest level has parentid = 0. So for example, ESP is the highest level node with parentid 0, 2nd level is account with its PARENTID as ESP's ENTITYID and so on...

Can you think of a recursive query that will return one set of data only based on the top level parentid? [Don't really need the very top level node returned although it being there as a part of the result set won't hurt] I want the result to show something like this:

101 Account 2 100
102 Site 3 101
103 Meter1 5 102
104 Meter2 5 102

When experimenting this is what I found :

db2 "select e1.ENTITYID, e1.ENTITYNAME, e1.parentid from entityrec e1 JOIN entityrec e2 ON e2.ENTITYID = e1.PARENTID"

gives this as a result --

ENTITYID ENTITYNAME PARENTID
----------- -------------------------------- -----------
101 Account 100
102 Site 101
103 Meter1 102
104 Meter2 102
201 Account2 200
202 Site2 201
203 Meter21 202
204 Meter22 202

AND this query :

db2 "select e1.ENTITYID, e1.ENTITYNAME, e1.parentid from entityrec e1 JOIN entityrec e2 ON e2.ENTITYID = e1.PARENTID AND e1.parentid=100"

returns this set :

ENTITYID ENTITYNAME PARENTID
----------- -------------------------------- -----------
101 Account 100

Any thoughts??
Last edited by db2user; 01-17-08 at 18:17.

2. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
Have a look at the DB2 manuals and search for "Bill of Materials". This is the exact same scenario and described in length there.

3. Registered User
Join Date
Dec 2002
Posts
123
Thank you stolze! I will check it out...

#### Posting Permissions

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