Preve all! There is a table

CREATE TABLE `hers` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_unicode_ci', `parent_id` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB AUTO_INCREMENT=0 ;

In which there is infa:

 id name parent_id 1 her1 NULL 2 her2 1 3 her3 1 4 her4 2 5 her5 4 6 her6 3

Question: How can I build a tree in one query without creating tables, view, etc? For example, how to find the root parent for id = 5? And how, for example, for him to find a non-indigenous, but second (after the indigenous) descendant? So that the result looks like this:

 id 1 2 4 5
  • With such an organization of the table - in any way, only if you replace the neighborhood list, for example, with nested sets, then the tree can be retrieved in one query. - cheops
  • @cheops, ingenious! It turns out that in this stupid table the author provided for the columns lft and rgt, it’s just that I wasn’t foolish for them to understand what they are for ^ _ ^ But for some reason he doesn’t have the level column, but what I need without it can be obtained. Who cares : zabolotnev.com/mysql-nested-sets and getinfo.ru/article610.html - nobody
  • Because it is considered that you can always calculate level, sometimes it is entered to cache the result, but if resources allow, you are more reliable to calculate. The canonical version stores only the left and right borders. - cheops

2 answers 2

As advised by @cheops, you can use NESTED SETS , in my case this is acceptable. Excellent mana with examples: http://zabolotnev.com/mysql-nested-sets and http://www.getinfo.ru/article610.html

    adjacency list and judging by ENGINE=InnoDB - mysql as a DBMS?

    The answer is no way.

    Not in a single query, but in a handful, but still at the SQL level — you can mess around with stored procedures. But mysql and stored procedures ... This is a rather sad topic.

    Better change or DBMS or tree storage model. From the DBMS, look at postgresql (besides the recursive CTE for solving your head-on problem, there is even a special ltree for storing trees). Of the tree models - Nested Set or Materialized Path - each has its own strengths and weaknesses, and one of them cannot be recommended as a silver bullet. See which one is best for your case.

    • Unfortunately, changing the DBMS is impossible. Apparently you have to either do some kind of view, or procedure: ( - nobody