Hello.
There is a table of headings in the MySQL database, each entry has a title, parent_id and id. Moreover, if parent_id = 0, then the rubric of the first level, and if parent_id = to some id, then this rubric is nested in another rubric for the corresponding id:

id | parent_id | title 1 | 0 | Корневая рубрика 1 2 | 0 | Корневая рубрика 2 3 | 0 | Корневая рубрика 3 15 | 1 | Подрубрика 15 в рубрике 1 16 | 1 | Подрубрика 16 в рубрике 1 17 | 3 | Подрубрика 17 в рубрике 3 18 | 2 | Подрубрика 18 в рубрике 2 19 | 1 | Подрубрика 19 в рубрике 1

Question: you need to display a list with headings and subheadings in this form:

 Корневая рубрика 1 - Подрубрика 15 - Подрубрика 16 - Подрубрика 19 Корневая рубрика 2 - Подрубрика 18 Корневая рубрика 3 - Подрубрика 17

Tell me, please, how to make a SELECT query correctly? Thank.

  • Do you have only 2 levels of nesting? And I understand correctly that you only need to correctly sort the records so that they go in the order you specified (for the "-" at the beginning of the line you can finish drawing by seeing that parent_id is not 0) - Mike
  • @Mike is only 2 levels so far; about it is more, until it is decided, it will probably be. Yes, just sort in the right order. Thank. - Alexander Vladimirovich

1 answer 1

For one level of nesting:

 select * from Table1 order by if(parent_id=0,id,parent_id), id

If there are more levels, then you should think about changing the database, because MySQL is practically the only DBMS that cannot work with hierarchical structures. Or implement the logic of building a tree on the client.

Test on sqlfiddle.com