0

See https://www.sitepoint.com/hierarchical-data-database-2/ for explanation of a MPTT.

I have a table that stores hierarchical data same as the link above except that it has an additional column called level to store the depth of the MPTT where level 0 is for the root node and the number increases for the descendant nodes.

Assuming I am using MySQL, what is the query to get the ancestor nodes given a node and the level we want?

E.g. in a 4 level hierarchical table, meaning level 0-3, and I give you a particular node which is at level 3 and I want to find its ancestors at level 1 only

How do I write a query like that?

Update:

I have been wrongly accused of duplicating this question How to create a MySQL hierarchical recursive query I will make it clearer how my question is different:

That question is asking and I quote "find its child ids". I am looking for the opposite.

I am looking for the ancestors nodes at a particular level given a node. That part of the question was bold. I even stated as much in my question title.

I will happily delete this question if I do find a duplicate that is asking the exact same thing.

Let me modify the example from http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ to make this clearer

enter image description here

+-------------+----------------------+--------+------+----+-----+
| category_id | name                 | parent | lft | rgt |level|
+-------------+----------------------+--------+-----+-----+-----+
|           1 | ELECTRONICS          |   NULL |   1 |  20 |  0  |
|           2 | TELEVISIONS          |      1 |   2 |   9 |  1  |
|           3 | TUBE                 |      2 |   3 |   4 |  2  |
|           4 | LCD                  |      2 |   5 |   6 |  2  |
|           5 | PLASMA               |      2 |   7 |   8 |  2  |
|           6 | PORTABLE ELECTRONICS |      1 |  10 |  19 |  1  |
|           7 | MP3 PLAYERS          |      6 |  11 |  14 |  2  |
|           8 | FLASH                |      7 |  12 |  13 |  3  |
|           9 | CD PLAYERS           |      6 |  15 |  16 |  2  |
|          10 | 2 WAY RADIOS         |      6 |  17 |  18 |  2  |
+-------------+----------------------+--------+-----+-----+-----+

My question would be given a node and the ancestor level, give me the ancestor node. In other words, if I was given the node 8 (which is the Flash) and also the level 1, I am expecting the answer to be 6 (the portable electronics)

I hope that makes it a lot clearer.

Kim Stacks
  • 10,202
  • 35
  • 151
  • 282
  • Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Jorge Campos Jul 17 '17 at 14:13
  • No it's not a duplicate. I quote from the question you cited that I am duplicating from "I should get all its child ids". I am looking to find the ancestors at a particular level. The complete opposite. – Kim Stacks Jul 17 '17 at 14:37
  • You can use the solution provided on that question to solve yours. It is the same problem, with the same solution. Have you at least tried it? – Jorge Campos Jul 17 '17 at 14:38
  • I'm sorry I disagreed that it's the same problem. It's true I am also using MPTT. It's not true the problem is the same. The question you quoted is looking for children given a node. I am looking for the ancestor given a node and the level of the ancestor. – Kim Stacks Jul 17 '17 at 14:46
  • Doesn't matter which direction you are doing the search. The query will be the same changing only the variables. Try it yourself and after doing so, if you still can't get it, you can add your attempt here. – Jorge Campos Jul 17 '17 at 14:49
  • As of other duplicates: https://stackoverflow.com/questions/14661525/mysql-find-parent-node ; https://stackoverflow.com/questions/1602536/mysql-nested-sets-how-to-find-parent-of-node ; even in other stack exchange sites: https://dba.stackexchange.com/questions/76280/getting-root-node-for-a-given-leafs-ids – Jorge Campos Jul 17 '17 at 15:03

0 Answers0