SELECT with unknown nesting?

Question

MySQL

Hey!
There is one geo table with cities, regions, districts, countries (id, type, name, id_parent).
Example:

1 | country  | Россия       | 0
2 | district | Крым         | 1
2 | area     | Большая Ялта | 2
3 | city     | Массандра    | 3

PS Nesting is not always 4-level, it can be just "country-city" or "country-region-city".
How to write a query that will select all cities given the country ID?

Answer 1

Made through three UNION.

Answer 2

1) In practice, it often happens that there is no need for a universal solution, since it is known that the nesting depth cannot exceed a certain reasonable value.
2) If nevertheless it is necessary universally... In MS SQL there is a convenient piece, CTE. There is nothing like this in MySql, so google "mysql recursive query"
3) Alternatively, write a stored function and use a cursor in it

Answer 3

Is mysql critical? Some DBMS have types for storing tree structures.
For example MSSQL (express free version) - https://msdn.microsoft.com/en-us/library/bb677290.aspx
or an overview here https://habrahabr.ru/post/27774/

Answer 4

I would split this one big table into several (countries, towns, streets/avenues/..., houses/apartments). It will be much easier to work and get rid of a lot of nesting.
Take KLADR as an example.