regionId parentRegionId   regionType    
100001  101322................3       
101322  100436................2       
100436  101218................1      
101218  0.....................0     

 id........City......State....Country.....Continent
100001..100001.........101322..100436......101218
101322  0            101322 	100436     101218
100436  0            0 	        100436     101218
101218  0            0	        0          101218

SELECT 
r.type,
r.region_no 
, case    when r.type = 0 then 0 # City
          when r.type = 1 then 0
          when r.type = 2 then 0
          when r.type = 3 then r.region_no
  END as cyty_region_id
, case    when r.type = 0 then 0 # State
          when r.type = 1 then 0
          when r.type = 2 then  r.region_no
          when r.type = 3 then  r.parent_region_no 
  END as state_region_id 
, case    when r.type = 0 then 0 # Country
          when r.type = 1 then r.region_no 
          when r.type = 2 then r.parent_region_no 
          when r.type = 3 then f.parent_region_no 
   END as country_region_id
 , case   when r.type = 0 then r.region_no # Continent
          when r.type = 1 then r.parent_region_no 
          when r.type = 2 then f.parent_region_no 
          when r.type = 3 then s.parent_region_no 
    END as continent_region_id   
FROM region_tbl r
LEFT OUTER JOIN region_tbl f ON r.parent_region_no = f.region_no
LEFT OUTER JOIN region_tbl s ON f.parent_region_no = s.region_no
limit 1000;