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

0 Answers