Back

Answered by battlethechamp

select o.id, o.name, w.revenue_billions
from wayne_enterprises as o inner join wayne_enterprises_org as w 
on w.id = o.id 
where parent_id = 1 AND W.ID != 2 
ORDER BY 
  CASE w.id
    WHEN 4 THEN 1
    WHEN 3 THEN 2
    WHEN 10 THEN 3
    end
;

Answered by vick0123

select o.id, o.name, w.revenue_billions
from wayne_enterprises as o inner join wayne_enterprises_org as w 
on w.id = o.id 
where parent_id = 1 AND W.ID != 2 
ORDER BY 
  CASE w.id
    WHEN 4 THEN 1
    WHEN 3 THEN 2
    WHEN 10 THEN 3
    end
;

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;