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 ;
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 ;
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;
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;
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;
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;