WITH CTE as (
  SELECT child_id,parent_id
  FROM Hierarchy
  where parent_id ='Africa'

  UNION ALL

  SELECT Hierarchy.child_id,Hierarchy.parent_id
  FROM Hierarchy
    inner join CTE on Hierarchy.parent_id=cte.child_id
  where Hierarchy.parent_id !='Africa'
)
SELECT *
FROM CTE
OPTION (MAXRECURSION 0)