Back

Answered by Waytan

with recursive cte (id,name, revenue_billions) as (
  -- initialization
   select 
      tmp.id, 
      tmp.name,
      revenue_billions 
      from wayne_enterprises a
      join wayne_enterprises_org b
      on b.parent_id = a.id 
      join wayne_enterprises tmp on tmp.id = b.id
      where a.name ='Wayne Research Institute'
  UNION ALL 
  -- recursive execution
  select 
      b1.id, 
      tmp1.name,
      b1.revenue_billions 
      from wayne_enterprises a1
      join wayne_enterprises_org b1
      on b1.parent_id = a1.id 
      join wayne_enterprises tmp1 on tmp1.id = b1.id
  	  join cte
  		on a1.name = cte.name
) select * from cte;

Answered by gurneet kaur

with recursive cte (id,name, revenue_billions) as (
  -- initialization
   select 
      tmp.id, 
      tmp.name,
      revenue_billions 
      from wayne_enterprises a
      join wayne_enterprises_org b
      on b.parent_id = a.id 
      join wayne_enterprises tmp on tmp.id = b.id
      where a.name ='Wayne Research Institute'
  UNION ALL 
  -- recursive execution
  select 
      b1.id, 
      tmp1.name,
      b1.revenue_billions 
      from wayne_enterprises a1
      join wayne_enterprises_org b1
      on b1.parent_id = a1.id 
      join wayne_enterprises tmp1 on tmp1.id = b1.id
  	  join cte
  		on a1.name = cte.name
) select * from cte;

Answered by Pfwd44

with recursive cte (id,name, revenue_billions) as (
  -- initialization
   select 
      tmp.id, 
      tmp.name,
      revenue_billions 
      from wayne_enterprises a
      join wayne_enterprises_org b
      on b.parent_id = a.id 
      join wayne_enterprises tmp on tmp.id = b.id
      where a.name ='Wayne Research Institute'
  UNION ALL 
  -- recursive execution
  select 
      b1.id, 
      tmp1.name,
      b1.revenue_billions 
      from wayne_enterprises a1
      join wayne_enterprises_org b1
      on b1.parent_id = a1.id 
      join wayne_enterprises tmp1 on tmp1.id = b1.id
  	  join cte
  		on a1.name = cte.name
) select * from cte;

Answered by anonymousUser

with recursive cte (id,name, revenue_billions) as (
  -- initialization
   select 
      tmp.id, 
      tmp.name,
      revenue_billions 
      from wayne_enterprises a
      join wayne_enterprises_org b
      on b.parent_id = a.id 
      join wayne_enterprises tmp on tmp.id = b.id
      where a.name ='Wayne Research Institute'
  UNION ALL 
  -- recursive execution
  select 
      b1.id, 
      tmp1.name,
      b1.revenue_billions 
      from wayne_enterprises a1
      join wayne_enterprises_org b1
      on b1.parent_id = a1.id 
      join wayne_enterprises tmp1 on tmp1.id = b1.id
  	  join cte
  		on a1.name = cte.name
) select * from cte;