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

Improve this question
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? Commented Mar 19, 2020 at 7:59
Related questions
4
Using CONNECT BY to get all parents and one child in Hierarchy through SQL query in Oracle
4
Connect by clause to get the top of hierarchy
0
Oracle Hierarchical Query Connect by Parent Child Tree Unknown level deep?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.