How to create a cascading query finding parent/child rows from table
with table1 as 
(
  select 1 as id, 'COUNTRY' as lvl, 'UK' as value, null as parentid from dual union all
  select 2 as id, 'COUNTRY' as lvl, 'FRA' as value, null as parentid from dual union all
  select 3 as id, 'CITY' as lvl, 'LONDON' as value, 1 as parentid from dual union all
  select 4 as id, 'CITY' as lvl, 'PARIS' as value, 2 as parentid from dual union all
  select 5 as id, 'RIVER' as lvl, 'SEINE' as value, 4 as parentid from dual union all
  select 6 as id, 'RIVER' as lvl, 'THAMES' as value, 3 as parentid from dual
)
select value from table1
connect by id = prior parentid
start with value = 'THAMES';

VALUE 
------
THAMES
LONDON
UK