Build a tree from Oracle tables
NickName:Alfredo Garcia Ask DateTime:2016-04-05T03:05:45

Build a tree from Oracle tables

I have two tables in Oracle for representing various trees, the tables are: "PARTS_TREE_ENTRIES" where all the nodes are stored (including parents and children) and "PARTS_ITEMS" wich describes the relation between the nodes

tables

In the table TREE_ITEMS the column COMPONENT_ID represents the father and COMPONENT_ITEMID its child

There are more than one tree and the nodes of all the trees are in the same table "TREE_ENTRIES" To make it easier to undestand this is a representation of a couple of trees:

enter image description here

And these are their entries in the tables:

enter image description here

enter image description here

As you can see in the table TREE_ITEMS the nodes that are the root of a branch have the value "A" for the COMPONENT_ID

I need help to build a query to get a list of all the nodes of the last level with its parents and its ID, the output should be similar to the following:

enter image description here

I've read about the clause "Connect by" but I've never used it and I dont where to start.

Thank you very much in advance!

Copyright Notice:Content Author:「Alfredo Garcia」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/36410849/build-a-tree-from-oracle-tables

More about “Build a tree from Oracle tables” related questions

Build a tree from Oracle tables

I have two tables in Oracle for representing various trees, the tables are: "PARTS_TREE_ENTRIES" where all the nodes are stored (including parents and children) and "PARTS_ITEMS" wich describes the

Show Detail

Create Tree from multiple tables in Oracle Apex

I am creating a Tree from two tables: DOC_RLSE with column IWR_NO (num), IWR_Desc (char) DOC_RLSE_DTL with column IWR_NO (num), ELM_NAME (char) I have reffered a good Apex application acc...

Show Detail

Oracle - Design of data warehouse tables

I understand that it makes sense to store dimension tables as index organized tables and fact tables using bitmap indexes for the dimension columns, however what should be done in the scenario wher...

Show Detail

Oracle APEX Tree Hierarchy Limitations/Issues

I am using Oracle APEX 4.2.2 and have constructed a Tree region based off a view. Now when I take this query (see below) and run this query say in Oracle SQL Developer - all is fine but when I place

Show Detail

Build Tree multidimensional array from database

How would I make an infinite recursion loop using php and mysql to build a specific array layout? I have writting out my tables, array layout and my attempt at getting this work. I have spent the l...

Show Detail

Oracle ADF: Tree Navigation Oracle Alta UI Design Pattern

I have created page with tree navigation with the help of the following blog. Implementing the Tree Navigation Oracle Alta UI Design Pattern The blog is really very good. I have created the follo...

Show Detail

Searching from all tables in oracle

I am developing a java app which can connect to oracle database and selecting column names from any tables, after selecting columns i have to query the data from those tables which the user select ...

Show Detail

Oracle Tree Hierarchy Displayed as an Oracle Report

I am researching how to best represent an Oracle Tree Hierarchy as an Oracle Report within Oracle APEX - something like a tree grid. As the data I am trying to display maybe up to 10 levels deep, i...

Show Detail

Naming tables returned from Oracle

I'm using the Oracle data libraries in .NET to get multiple tables returned from a Procedure (as multiple Out cursors). This data is being inserted into a DataSet. The problem I'm having is that the

Show Detail

Build a paginated tree in SQL Server with multiple tables

Let's say I have a tree of companies and I need to list the products that each one of them has in their building, like this: Owner Acessories Keyboard Mouse Furnitues Chair Tabl...

Show Detail