Oracle Sql. Recursive select with join

Question

I have a table with applications and table with messages. Applications has hierarchical structure, e.g each application has a parent. And I have a table with messages. Each message has a key and an application id. I want to be able to select message by it's key. If it's found for current application then return it, if no then try to find it with parent id. Apps table:

id | name | parentId
--------------------
1  |parent| NULL
2  |child | 1

Msg table:

key                  | text           | app
-------------------------------------------------
overriden.system.msg | some text      | 1
parent.msg           | parent txt     | 1
overriden.system.msg | overriden text | 2

So if I'm in the child app(2) on key overriden.system.msg I want to get overriden text. On key parent.msg I want to get parent txt. I know that it must be done with cte, but I have very little expirience with sql and cte is mind-blowing for me right now. Could you please provide working query for this situation? Or maybe you have a better vision how to achieve such functionality without recursion?

dcieslak · Accepted Answer · 2016-02-15 09:23:47Z

The above should work fine:

with app_tree (id, app, lvl) as
 ( select id , parentID , 0
    from  Apps 
    where id = 2
   union all
    select t.app, a.parentID, t.lvl + 1 from 
     Apps a 
     join app_tree t
     on t.app = a.id  ),
  all_msg as (
  select key, text, 
     row_number() over ( partition by key  order by lvl) overrideLevel
      from app_tree a
  join msg m
   on m.app = a.id )
  select  * from all_msg where
     overrideLevel =1

It returns:

    KEY                     TEXT
  -------------------------------------
    overriden.system.msg    overriden text  
    parent.msg              parent txt

First it gets the list of all applications for specified id using recursive query with parentid. After that it gets a list of all functions for all applications and generates increasing numbers for the same keys basing on the level. At the end it just takes the first possible level and ignore all parent's levels for the same key.

SkyWalker · Accepted Answer · 2016-02-15 10:23:41Z

1

Mb it will help you:

with app (id, name, parent_id) as 
      (select 1, 'parent', null from dual union all
       select 2, 'child', 1 from dual)
    ,msg (key, text, app) as 
      (select 'overriden.system.msg', 'some text', 1 from dual union all
       select 'parent.msg', 'parent txt', 1 from dual union all
       select 'overriden.system.msg', 'overriden text', 2 from dual)

select key
      ,max(text) keep (dense_rank last order by nvl2(text,level,0)) msg
from
 (select *
  from app a
  join msg m on (a.id = m.app)) v
start with v.parent_id is null
connect by prior v.id = v.parent_id and prior v.key = v.key
group by key

answered Feb 15, 2016 at 10:23

SkyWalker

4942 silver badges7 bronze badges

Collectives™ on Stack Overflow

Oracle Sql. Recursive select with join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related