Topmost parent in a child parent relationship table using query

Question

I have a table having parent child relationship :

Parent_id Child_id
1 2
1 5
2 3
2 4
5 6
7 4
7 8 
8 6

The relation is between child and immediate parent.

There can be multiple parents of a child like 4 is child of 2 and 7

How can I find the top most parent of a child using sql query?

EXAMPLE : for 3 the answer has to be 1

for 4 the answer can be 1 or 7. for simplicity lets assume I have to return the smallest one i.e 1

for 5 the answer is 1(because 1 is having no parent, so it will be considered topmost)

I am using DB2 (10.5) but a general sql query will give me the idea.

You can use recursive sql [like this](http://stackoverflow.com/questions/17676944/finding-a-top-level-parent-in-sql) — Mahesh, Nov 23 '16 at 03:23
Check out http://stackoverflow.com/questions/17676944/finding-a-top-level-parent-in-sql — MichaelTiefenbacher, Nov 23 '16 at 16:58

score 0 · Answer 1 · answered Nov 23 '16 at 09:24

a possibility in db2 (doesnt work on MySQL)

SELECT CHILD_ID, min(ROOT) as ROOT FROM (         
  SELECT CONNECT_BY_ROOT PARENT_ID AS ROOT, f1.* 
  FROM YOURTABLE f1                       
  CONNECT BY NOCYCLE PRIOR CHILD_ID=PARENT_ID     
) tmp                                         
group by CHILD_ID

Topmost parent in a child parent relationship table using query

1 Answers1