oracle 9i get highest member of tree with given child

Question

I have a parent-child relationship in an Oracle 9i database-table

like:

parent | child  
1      | 2  
2      | 3
2      | 4
null   | 1
1      | 8

I need to get the absolute parent from a given child. Say, I have child 4, it has to give me parent: 1

I already looked to CONNECT BY , but I can't find the solution.

Answer 1

you could use a CONNECT BY query to build the list of parents and then filter :

SQL> WITH tree AS (
  2     SELECT 1 parent_id, 2 child_id FROM DUAL
  3     UNION ALL SELECT 2   , 3  FROM DUAL
  4     UNION ALL SELECT 2   , 4  FROM DUAL
  5     UNION ALL SELECT null, 1  FROM DUAL
  6     UNION ALL SELECT 1   , 8  FROM DUAL
  7  )
  8  SELECT child_id
  9    FROM (SELECT *
 10            FROM tree
 11          CONNECT BY PRIOR parent_id = child_id
 12           START WITH child_id = 4)
 13   WHERE parent_id IS NULL;

  CHILD_ID
----------
         1