Get Parent and grand parents of a particular child

Question

i have table like below

tablename :ExampleTable

ChildID       ChildCommonID   ParentID


1               2                0

2               3                0

3               4                1

4               5                3

5               6                4

The Problem is :

i have a child id example :ChildID= 5

so i need to check wheather it has a parent or not if it contain a parent then check the corresponsing parentid, in this case the parentID is 4 so need to check the child 4 has any parent ,in this case parentID of child 4 is 3, so gain check child 3 has any parent in this case child 3 has parent 1, so check child 1 has any parents here child 1 is the top grand parent and it has no parent so stop the process and return all childids up to 1

Here the expected output is

ChildID

5
4
3
1

i had tried something like below but it does not give correct output

with getallparent as (
   select * 
   from ExampleTable 
   where ChildID  = 5 
  union all 
  select *
   from ExampleTable c 
     Left join getallparent p on p.ChildID = c.ParentID 
) 
select *
from getallparent;

If you need the sample data you can use the below query

create table ExampleTable(ChildID int,ChildCommonID int ,ParentID int )
insert into ExampleTable values(1,2,0)
insert into ExampleTable values(2,3,0)
insert into ExampleTable values(3,4,1)
insert into ExampleTable values(4,5,3)
insert into ExampleTable values(5,6,4)

Any help will be appreciated

Answer 1

Like my comment, your columns were incorrect in join. Use getallparent p on p.ParentID = c.ChildID.

with getallparent as (
   select ChildID,ParentID
   from ExampleTable 
   where ChildID  = 5 
  union all 
  select C.ChildID,C.ParentID
   from ExampleTable c 
     inner join getallparent p on p.ParentID = c.ChildID 
) 
select *
from getallparent;

Fiddle