There is a navigation table

navigations id parent_id name

The table is linked with navigations.parent_id on navigations.id

How to write a query that will recursively search for a name in it or in its parents?

Suppose my table consists of the following columns with values:

 id parent_id name 1 0 aaa 2 1 bbb 3 2 ccc

the value of the name column is unique, not repeated.

It is necessary to find name = 'aaa' where id = 3, if it does not find it, continue to search in its parent (id = 2) , and so on until it gets to id = 1 where name= 'aaa' because id=3 is in its parent chain

Need a query at the level of sql.

here is my code. But it does not work as I need:

 SELECT `pn`.`id` AS `parent_id` FROM `navigations` `pn` INNER JOIN `navigations` `ch_n` ON `ch_n`.`parent_id` = `pn`.`id` WHERE `ch_n`.`name` = 'aaa'

With the help of this query at the PHP level, I can implement it, but I need it at the query level. To get the ready result.

Is it possible ?

Thank you for your help

    1 answer 1

    Mysql does not know how to recursive queries, one query is impossible. You can get out through joins if you know exactly the nesting level of the current element from the root.

    Can be implemented on stored procedures. For sample code, https://dba.stackexchange.com/a/7161

    • Yes, in principle, I already understood, but still hoped that there would be :). Thanks for the link - Vanya Avchyan