MySQL query to select cities under any region given

Question

I have a database of all regions including cities they are arranged in a tree structure using each ones id as parent region id.

mysql columns are id , parent_region_id , region_name

Structure is like ...

SELECT region_id, region_type, region_name, parent_region_id, parent_region_name FROM regions_data WHERE parent_region_id = 201

Region Types are ....

Country -> Province (State) -> Multi-Region (within a country) -> Multi-City (Vicinity) -> City

Only some countries have Province (State), other countries having Multi-Region (within a country).. and these both may or may not have Multi-City (Vicinity) level under it.

US -> California -> Central California -> San Joaquin Valley -> Mojave & Vicinity -> California City

Like above there will be lot of regions under US and all those regions may have lot of sub regions and cities under those sub regions.

I want a perfect MySQL query which will return all the cities inside the given regions.

Say Give me all cities under US OR Give me all cities under ALASKA STATE

Is there any MySQL experts here to help me ?

Answer 1

You can use several JOINs to get through the sub-levels, and then print only those that have the correct region_type you're looking for. Assuming that you have a maximum of 6 levels (you can add more if needed pretty easily...):

SELECT 
  IF(`t1`.`region_type`='City', `t1`.`region_name`,
    IF(`t2`.`region_type`='City', `t2`.`region_name`,
      IF(`t3`.`region_type`='City', `t3`.`region_name`,
        IF(`t4`.`region_type`='City', `t4`.`region_name`,
          IF(`t5`.`region_type`='City', `t5`.`region_name`, '')
        )
      )
    )
  )
FROM `regions_data`

LEFT JOIN `regions_data` `t1` ON (`t1`.`parent_region_id` = `regions_data`.`id`)
LEFT JOIN `regions_data` `t2` ON (`t2`.`parent_region_id` = `t1`.`id`) 
LEFT JOIN `regions_data` `t3` ON (`t3`.`parent_region_id` = `t2`.`id`) 
LEFT JOIN `regions_data` `t4` ON (`t4`.`parent_region_id` = `t3`.`id`) 
LEFT JOIN `regions_data` `t5` ON (`t5`.`parent_region_id` = `t4`.`id`) 

WHERE `regions_data`.`region_name` = 'ALASKA STATE'