getting values related on a hierarchy from a database table

Question

I have a simple table in DB2 with the following structure:

ID  |  Description |  ParentID
01  |  Cat         |  101
02  |  Salmon      |  201
03  |  Dog         |  101
101 |  Mammals     |  301
201 |  Fish        |  201
301 |  Vertebrates |  401
401 |  Animals     |

This describes a hierarchical structure from the leaf records (in this case dog and cat) all the way up to animals.

How could I get all the descriptions for a given ID up to the topmost description (the one not getting any parentID in it)? for example, for ID 01, I would like to get: "Cat Mammals Vertebrates Animals"

You can do multiple selects in your application until you get an empty ParentID. or use the [while loop](https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_whilestatement4nativesqlpl.html) in your sql — Ivonet, Dec 10 '17 at 23:00
Possible duplicate of [Recursive select?](https://stackoverflow.com/questions/10191746/recursive-select) — Gholamali Irani, Dec 10 '17 at 23:38

score 1 · Answer 1 · answered Dec 11 '17 at 02:32

1

I think you will need a Connect by Prior clause to initiate the recursion:

Select Description
From table
Start with ID = 01
Connect By Prior ID = ParentID

https://www.ibm.com/developerworks/ibmi/library/i-db2connectby/index.html

answered Dec 11 '17 at 02:32

McClAnalytics

330
1
8

Also I wasn't sure what to put for the name of the table. So I made it..."table." – McClAnalytics Dec 11 '17 at 02:33

getting values related on a hierarchy from a database table

1 Answers1