Traversing trees in SQL: JOINs vs imperative algorithm
There are several models for holding hierarchical data in a relational database. There's a good summary here. In summary they are


Adjacency list: each row contains a foreign key to its parent. Fast for structure changes. Requires recursive processing to find the base ancestor.
Path enumeration: each node stores its ancestry. An update requires all children to be updated. For a known node reaching the root requires no further work.
Nested sets: additional columns hold the ancestry. The cost of structure changes depends on how big the moving subtree is. The root node is fast to find.
Closure table: store all ancestor-descendant pairs. Structure changes can be expensive. Root node expensive to identify.


From the given information a path enumeration sounds like your best option.