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" which describes the relation between the nodes

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 understand this is a representation of a couple of trees:

And these are their entries in the tables:

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:

I've read about the clause "Connect by", but I've never used it and I don't know where to start.

Thank you very much in advance!

I realize you explicitly asked about a connect by query, but I thought we could practice using Recursive With SQL, as it does the same job with some added functionality.

My best guess would be something like this given your sample result.

It wasn't clear to me whether you wanted concatenated data or separated columns, so there's a suggestion for both cases.

with tree_view(tree_id, component_id, component_itemid, id, part_tree_id, componentid, name, lvl, tree_path, root_id) as (
select t.tree_id, t.component_id, t.component_itemid, t.id, e.part_tree_id, e.componentid, e.name, 1, e.name, component_itemid
  from tree_items t,
       tree_entries e
 where t.component_itemid = e.componentid
   and t.tree_id = e.part_tree_id
   and t.component_id = 'A'
union all
select t.tree_id, t.component_id, t.component_itemid, t.id, e.part_tree_id, e.componentid, e.name, tv.lvl + 1, tv.tree_path || '=>' || e.name, tv.root_id
  from tree_items t,
       tree_entries e,
       tree_view tv
 where to_char(tv.component_itemid) = to_char(t.component_id)
       and to_char(e.componentid) = to_char(t.component_itemid)
       and tv.tree_id = t.tree_id
) -- end of hierarchy view
search depth first by lvl set order1
select tree_path,
       name,
       componentid,
       regexp_substr(tree_path, '[[:alpha:]]+', 1, 1) lvl1_part,
       regexp_substr(tree_path, '[[:alpha:]]+', 1, 2) lvl2_part,
       regexp_substr(tree_path, '[[:alpha:]]+', 1, 3) lvl3_part -- add more if there are further levels down the tree
  from (
        select tree_id, component_id, component_itemid, id, part_tree_id, componentid, name, lvl, tree_path, root_id, order1,
               case when lvl - lead(lvl) over (order by order1) < 0 then 0 else 1 end is_leaf
          from tree_view
       )
 where is_leaf = 1;

Here is a sample execution on Oracle using your supplied data:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as sitja@orasitja

SQL> 
SQL> col tree_path format a40
SQL> col lvl1_part format a20
SQL> col lvl2_part format a20
SQL> col lvl3_part format a20
SQL> drop table tree_entries;
Table dropped
SQL> create table  tree_entries as
  2  with tree_entries(part_tree_id, componentid, name) as (
  3  select 1, 101, 'CLOCK' from dual union all
  4  select 1, 102, 'WATCH' from dual union all
  5  select 1, 105, 'BAND' from dual union all
  6  select 1, 113, 'MATERIAL' from dual union all
  7  select 1, 114, 'COLOR' from dual union all
  8  select 1, 106, 'CASE' from dual union all
  9  select 1, 115, 'MATERIAL' from dual union all
 10  select 1, 116, 'SHAPE' from dual union all
 11  select 1, 107, 'BEZEL' from dual union all
 12  select 1, 117, 'MATERIAL' from dual union all
 13  select 1, 118, 'TEXTURE' from dual union all
 14  select 1, 108, 'FACE' from dual union all
 15  select 1, 119, 'SHAPE' from dual union all
 16  select 1, 120, 'DESIGN' from dual union all
 17  select 2, 103, 'RELOJ' from dual union all
 18  select 2, 104, 'RELOJPULSERA' from dual union all
 19  select 2, 109, 'CORREA' from dual union all
 20  select 2, 121, 'MATERIAL' from dual union all
 21  select 2, 122, 'COLOR' from dual union all
 22  select 2, 110, 'CAJA' from dual union all
 23  select 2, 123, 'MATERIAL' from dual union all
 24  select 2, 124, 'FORMA' from dual union all
 25  select 2, 111, 'BISEL' from dual union all
 26  select 2, 125, 'MATERIAL' from dual union all
 27  select 2, 126, 'TEXTURA' from dual union all
 28  select 2, 112, 'CARATULA' from dual union all
 29  select 2, 127, 'FORMA' from dual union all
 30  select 2, 128, 'DISEÑO' from dual
 31  )
 32  select * from tree_entries;
Table created
SQL> drop table tree_items;
Table dropped
SQL> create table  tree_items as
  2  with tree_items(tree_id, component_id, component_itemid, id) as (
  3  select 1, 'A', 101, 1 from dual union all
  4  select 1, 'A', 102, 2 from dual union all
  5  select 1, '101', 107, 3 from dual union all
  6  select 1, '101', 108, 4 from dual union all
  7  select 1, '102', 105, 5 from dual union all
  8  select 1, '102', 106, 6 from dual union all
  9  select 1, '107', 117, 7 from dual union all
 10  select 1, '107', 118, 8 from dual union all
 11  select 1, '108', 119, 9 from dual union all
 12  select 1, '108', 120, 10 from dual union all
 13  select 1, '105', 113, 11 from dual union all
 14  select 1, '105', 114, 12 from dual union all
 15  select 1, '106', 115, 13 from dual union all
 16  select 1, '106', 116, 14 from dual union all
 17  select 2, 'A', 103, 15 from dual union all
 18  select 2, 'A', 104, 26 from dual union all
 19  select 2, '103', 111, 33 from dual union all
 20  select 2, '103', 112, 42 from dual union all
 21  select 2, '104', 109, 54 from dual union all
 22  select 2, '104', 110, 62 from dual union all
 23  select 2, '111', 125, 74 from dual union all
 24  select 2, '111', 126, 82 from dual union all
 25  select 2, '112', 127, 91 from dual union all
 26  select 2, '112', 128, 10 from dual union all
 27  select 2, '109', 127, 114 from dual union all
 28  select 2, '109', 122, 122 from dual union all
 29  select 2, '110', 123, 3334 from dual union all
 30  select 2, '110', 124, 141 from dual
 31  )
 32  select * from tree_items;
Table created
SQL> with tree_view(tree_id, component_id, component_itemid, id, part_tree_id, componentid, name, lvl, tree_path, root_id) as (
  2  select t.tree_id, t.component_id, t.component_itemid, t.id, e.part_tree_id, e.componentid, e.name, 1, e.name, component_itemid
  3    from tree_items t,
  4         tree_entries e
  5   where t.component_itemid = e.componentid
  6     and t.tree_id = e.part_tree_id
  7     and t.component_id = 'A'
  8  union all
  9  select t.tree_id, t.component_id, t.component_itemid, t.id, e.part_tree_id, e.componentid, e.name, tv.lvl + 1, tv.tree_path || '=>' || e.name, tv.root_id
 10    from tree_items t,
 11         tree_entries e,
 12         tree_view tv
 13   where to_char(tv.component_itemid) = to_char(t.component_id)
 14         and to_char(e.componentid) = to_char(t.component_itemid)
 15         and tv.tree_id = t.tree_id
 16  ) -- end of hierarchy view
 17  search depth first by lvl set order1
 18  select tree_path,
 19         name,
 20         componentid,
 21         regexp_substr(tree_path, '[[:alpha:]]+', 1, 1) lvl1_part,
 22         regexp_substr(tree_path, '[[:alpha:]]+', 1, 2) lvl2_part,
 23         regexp_substr(tree_path, '[[:alpha:]]+', 1, 3) lvl3_part -- add more if there are further levels down the tree
 24    from (
 25          select tree_id, component_id, component_itemid, id, part_tree_id, componentid, name, lvl, tree_path, root_id, order1,
 26                 case when lvl - lead(lvl) over (order by order1) < 0 then 0 else 1 end is_leaf
 27            from tree_view
 28         )
 29   where is_leaf = 1;
TREE_PATH                                NAME         COMPONENTID LVL1_PART            LVL2_PART            LVL3_PART
---------------------------------------- ------------ ----------- -------------------- -------------------- --------------------
CLOCK=>BEZEL=>MATERIAL                   MATERIAL             117 CLOCK                BEZEL                MATERIAL
CLOCK=>BEZEL=>TEXTURE                    TEXTURE              118 CLOCK                BEZEL                TEXTURE
CLOCK=>FACE=>SHAPE                       SHAPE                119 CLOCK                FACE                 SHAPE
CLOCK=>FACE=>DESIGN                      DESIGN               120 CLOCK                FACE                 DESIGN
WATCH=>BAND=>MATERIAL                    MATERIAL             113 WATCH                BAND                 MATERIAL
WATCH=>BAND=>COLOR                       COLOR                114 WATCH                BAND                 COLOR
WATCH=>CASE=>MATERIAL                    MATERIAL             115 WATCH                CASE                 MATERIAL
WATCH=>CASE=>SHAPE                       SHAPE                116 WATCH                CASE                 SHAPE
RELOJ=>BISEL=>MATERIAL                   MATERIAL             125 RELOJ                BISEL                MATERIAL
RELOJ=>BISEL=>TEXTURA                    TEXTURA              126 RELOJ                BISEL                TEXTURA
RELOJ=>CARATULA=>FORMA                   FORMA                127 RELOJ                CARATULA             FORMA
RELOJ=>CARATULA=>DISEÑO                  DISEÑO               128 RELOJ                CARATULA             DISEÑO
RELOJPULSERA=>CORREA=>COLOR              COLOR                122 RELOJPULSERA         CORREA               COLOR
RELOJPULSERA=>CORREA=>FORMA              FORMA                127 RELOJPULSERA         CORREA               FORMA
RELOJPULSERA=>CAJA=>MATERIAL             MATERIAL             123 RELOJPULSERA         CAJA                 MATERIAL
RELOJPULSERA=>CAJA=>FORMA                FORMA                124 RELOJPULSERA         CAJA                 FORMA
16 rows selected

SQL>