Is there any effective way to, without limiting the depth, retrieve a single path in a Adjacency List model based on the node's ID? Like if I've got an ID for a node named "Banana" I could get the following Path: Food > Fruits > Banana
It's not a big problem if it's impossible, but I thought about if it could be possible to run joins through a while-loop or something? Until the parent turns 0.
No, not in MySQL at least. That is one of the biggest limitations of the Adjacency List Model.
You could continue to self join a finite number of times, but that's ugly, awkward and doesn't cover unlimited dept. You could also download all the data in your application, build a tree, and find the path in the application.
Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g, support recursive queries using common table expressions with the WITH keyword. This feature allows queries such as this to be written with ease, but unfortunately MySQL does not support recursive queries yet.
You may may be interested in checking out the following article which describes an alternative model (the nested set model), which makes recursive operations easier (possible) in MySQL:
In addition, I also suggest checking out the following presentation by @Bill Karwin, a regular contributor on Stack Overflow:
The closure table model described in the presentation is a very valid alternative to the nested set. He describes this model in further detail in his SQL Antipatterns book (excerpt from the chapter on this topic).
No, MySQL doesn't have recursive queries like PostgreSQL, Oracle or SQL Server. The adjacency list model isn't a great model when using MySQL, a nested set is a better (but more complex) one.
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html