Selection - parent, ancestor, ancestor of ancestor, etc.

Question

MySQL

Good afternoon!

Immediately I apologize for the "pun" in the title and move on to the question.

There is a table:

id	parent_id	name
one	0	a
2	one	b
3	one	in
4	3	G

Whether it is possible to select the parent and all its ancestors and ancestors of ancestors as one request?

There can be any number of levels.

Answer 1

You need to use recursive queries, but MySQL does not support them, as far as I know. Perhaps this article will help you: habrahabr.ru/blogs/sql/43955/.

Answer 2

Read this article explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

Answer 3

in short, with your limitations and the chosen method of storing the tree, it is impossible. More details in the links in the posts above. It is necessary to slightly change the tree storage architecture.