CODEWITHSUNDEEP
mysqlparent-childjooqhierarchical-datarecursive-query
sliceh
sliceh

Reputation: 13

Need sql query to find the lowest level of values in a hierarchy

Table Organization(organizationId, parentId, name) Table Setting(id, organizationId, settingName, settingValue)

Here in |a,1| : a is settingName, 1 is settingValue

Organization A   |a,1|    |b,2|    |c,3|    |d,4|
Organization B            |b,5|
Organization C   |a,8|
Organization D                     |c,2|

A->B->C->D (hierarchy) Now get query of organization D should give me (a,8)(b,5)(c,2)(d,4)

My query :

with recursive cte as (
select
    *, 1 as level
from
    Organization
where
    organizationId = 3
union ALL 
select
    t.*, cte.level+1
from
    cte
join Organization t on
    t.OrganizationId = cte.parentId )
-- select
    --  ss.OrganizationId,ss.parentId, ss.settingName,ss.settingValue
-- from 
    select
        cte.OrganizationId,
        cte.ParentId,
        s.settingName ,
        s.settingValue,
        level
    from
        cte
    inner join Settings s on
        s.organizationId = cte.OrganizationId
        -- as ss group by ss.settingName

This gives me settings from all the organizations but i need the values of most recent child(if there is any) else from the parent (This should continue till the ROOT organization, bottom to up approach)

Settings Table Data

Organization table Data

Upvotes: 1

Views: 590

Answers (2)

ambreen irfan
ambreen irfan

Reputation: 1

Create schema

create table t42 (id number, parent_id number, flag varchar2(1), str
varchar2(20));

insert into t42 values (1, null, 'A', 'Parent');

insert into t42 values (2, 1, 'B', 'Child 1'); insert into t42
values (3, 1, 'C', 'Child 2'); insert into t42 values (4, 1, 'C',
'Child 3');

insert into t42 values (5, 2, 'D', 'Grandchild 1 1'); insert into
t42 values (6, 3, 'B', 'Grandchild 2 1'); insert into t42 values (7,
3, 'D', 'Grandchild 2 2');

now excecute below query

select t.id, t.parent_id, t.flag, t.str
from (
  select t.*, dense_rank() over (partition by flag order by lvl) as rn
  from (
    select t.*, level as lvl
    from t42 t
    start with parent_id is null
    connect by prior id = parent_id
  ) t
) t
where rn = 1
and flag = 'A';

select t.id, t.parent_id, t.flag, t.str
from (
  select t.*, dense_rank() over (partition by flag order by lvl) as rn
  from (
    select t.*, level as lvl
    from t42 t
    start with parent_id is null
    connect by prior id = parent_id
  ) t
) t
where rn = 1
and flag = 'B';

select t.id, t.parent_id, t.flag, t.str
from (
  select t.*, dense_rank() over (partition by flag order by lvl) as rn
  from (
    select t.*, level as lvl
    from t42 t
    start with parent_id is null
    connect by prior id = parent_id
  ) t
) t
where rn = 1
and flag = 'C';

select t.id, t.parent_id, t.flag, t.str
from (
  select t.*, dense_rank() over (partition by flag order by lvl) as rn
  from (
    select t.*, level as lvl
    from t42 t
    start with parent_id is null
    connect by prior id = parent_id
  ) t
) t
where rn = 1
and flag = 'D';

Upvotes: 0

Serg
Serg

Reputation: 22811

Keep track of orgId of interest and level of the setting. Find first value by level

with recursive cte as (
   select  s.*, 1 as level, s.organizationId  as orgId
   from    Settings s 
   where   s.organizationId = 4
   union ALL 
   select   s.*, cte.level+1, cte.orgId
   from cte 
   join    Organization t on t.organizationId = cte.organizationId 
   join    settings s on s.organizationId = t.ParentId  
)
select distinct orgid, settingname, first_value(settingvalue) over(partition by orgId, settingname order by level)
from cte

EDIT Case when an organization can have no settings

with recursive cte as (
   select  t.organizationId, s.settingname, s.settingvalue, 1 as level, t.organizationId orgId
   from    Organization t
   left join Settings s on t.organizationId = s.organizationId 
   where   t.organizationId = 5
   
   union ALL 
   
   select   t.ParentId, s.settingname, s.settingvalue, cte.level+1, cte.orgId
   from cte 
   join    Organization t on t.organizationId = cte.organizationId 
   left join    settings s on s.organizationId = t.ParentId  
)
select distinct orgid, settingname, first_value(settingvalue) over(partition by orgId, settingname order by level)
from  cte
where settingName is not null 
order by orgid, settingname;

db<>fiddle

Upvotes: 1

Related Questions