1

I have to find the top parent in a hierarchy where I provide the child. the top most parent will be when the id = ref

table example:

+--------------------+
| id parent_id ref   |
+--------------------+
| 1     1      1     |
| 2     1      1     |
| 3     2      1     |
| 4     3      1     |
| 5     4      2     |
+--------------------+

I need to get the topmost parent (id 1) when I enter id 4

i cannot simply do select * from table where ref = id as there may be other hierarchy in the same table so the query must do:

start with the id eg 4 check id_parent = ref if not equal then use parent_id as the new id and check if it is id_parent = ref and so on until parent_id = ref

also, id 5 is of a different hierarchy

Any help is greatly appreciated Thanks

kevin cutajar
  • 11
  • 1
  • *"id 5 is of a different hierarchy"* Does this mean the `ref 2` in that record is a different `id 2` from the one you show? How do these hierarchies work? – APC Mar 19 '20 at 07:59

0 Answers0