Results 1 to 3 of 3
  1. #1
    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 19:17.

  2. #2
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    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
  •