select ancestors using query like a recursive function

Question

I have a database table menu having id, name and parentid.

I have the following values in the database, I want to collect all fields including parent menus using query.

   id  name      parentid
   1   File         0
   2   New          1
   3   Document     2
   4   Image        2
   5   Edit         0
   6   Copy         5
   7   Paste        5

Example: I have 2 as my current menu, I want to select all the fields having parent Id 2 and their parent and their parents' parent until I reach the top parent (i.e. with parentid=0).

Is it possible to collect it using a single query? If yes, how it can be achieved?

Answer 1

If you have control over your data structure then there is a better way of storing this data which then lets you do what you need and it is all much easier than trying to carry on as you are.

What you are doing is commonly known as the adjacency list model. You should check out the nested sets model which is a much more efficient way of storing and retrieving hierarchical data.

There is a good tutorial here and a quick search on the web for Joe Celko will give you lots of links in the right direction as he has been writing about this for many years.

Hope this helps