0

I have a child-parent table like this (0 means that it is the top item):

itemID | parentItemId
---------------------
     1 |            0
     2 |            0
     3 |            0
     4 |            1
     5 |            1
     6 |            2
     7 |            5
     8 |            7
     9 |            7

I want to add a new column that will be rootItemId according to this logic:

If it is a top item then rootItemId = itemID, else rootItemId will be item under the root (i.e. level - 2) itemID.

It will look like this:

itemID | parentItemId | rootItemId 
----------------------------------
     1 |            0 |          1
     2 |            0 |          2
     3 |            0 |          3
     4 |            1 |          4
     5 |            1 |          5
     6 |            2 |          6
     7 |            5 |          5
     8 |            7 |          5
     9 |            7 |          5

I can get this result using the following sql:

      select itemID, 
             parentItemId, 
             itemID as rootItemId 
        from ItemTable
       where itemID = 0

       union

      select itemID, 
             parentItemId, 
             connect_by_root parentItemId as rootItemId
        from ItemTable
        start with parentItemId in 
           (select itemID 
            from ItemTable 
            where parentItemId =0)
       connect by prior itemID  =  parentItemId

Is there a more elegant and efficient way to achieve the same results?

Thanks

Nir
  • 601
  • 7
  • 21
  • Your query is fine. I prefer recursive CTEs (because they are standard SQL). They are available only in Oracle12C+ and I suspect they have the same performance. – Gordon Linoff May 14 '19 at 14:23
  • @GordonLinoff fwiw, [recursive subquery factoring](https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#i2077142) is available from 11gr2, although there were some enhancements in 12c – Boneist May 14 '19 at 14:38

0 Answers0