MySQL - Getting the top-level parent id in a hierarchy

Question

Here's my table structure...

TABLE : position_hierarchy_level

id parent_position_id position_id
 1 1                  2
 2 2                  3
 3 3                  4
 4 4                  5
 5 5                  6
 6 6                  7
 7 7                  8
 8 8                  9
 9 9                  10
 10 10                11
 11 11                12
 12 12                13
 13 13                14
 14 14                15

My query for getting the parent_position_id of a certain position_id is:

select `parent_position_id` from `position_hierarchy_level` where position_id= 15;

But how can I get the top-most parent of a certain position_id? For example, the top-most parent_position_id of position_id 15 would be 1.

Is there a convenient way to get this value using a single query? Or do I need to create a loop in PHP?

Answer 1

Your database structure wouldnt let you do it unless there are 15 or more joins. You are using Adjacency list model. Try using The nested set model

Here is an example with php