CODEWITHSUNDEEP
sqloracle-databasehierarchical-data
Zeus
Zeus

Reputation: 6586

Query hierarchical items in a table

We have a table in house which as hierarchical data in the following format. Items is the parent table with list of items. But, the relationship between items is maintained in std_item_rel table

Constraints:

How do I write a query to get all the subordinate values for item 123 from below sample schema?

items

item_id | item_id_sd|  item_desc
----------------------------
123     |  A        |  Some description

std_item_rel

std_item_dwn | item_id_sd_dwn | item_id_sd_up | std_item_up 
------------------------------------------------------------
123          |   A            |     null      | null
125          |   C            |     A         | 123
129          |   C            |     C         | 125

Update: Error I got was:

ORA-01436: CONNECT BY loop in user data
01436. 00000 -  "CONNECT BY loop in user data"
*Cause:    
*Action:

Fixed it by adding nocycle param in the connect by clause.

Upvotes: 1

Views: 350

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221380

CONNECT BY should allow for rather simple solutions, here:

SELECT     std_item_down
FROM       std_item_rel
CONNECT BY std_item_up = PRIOR std_item_down
START WITH std_item_down = 123

See also this SQLFiddle

Upvotes: 1

Related Questions