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??

Have a look at the DB2 manuals and search for "Bill of Materials". This is the exact same scenario and described in length there.

Thank you stolze! I will check it out...