create table parents(unit_id bigint identity(1,1),unit nvarchar(max),link_id bigint)
insert into parents values('kg',2)
insert into parents values('gm',3)
insert into parents values('mg',0)
insert into parents values('DZN',5)
insert into parents values('pcs',0)
just pass the parent_id :
with cte (link_id,unit_id,unit)
as
(
select link_id,unit_id,unit from parents where unit_id=1
union all
select b.link_id,b.unit_id,b.unit from cte as a inner join parents as b on a.link_id=b.unit_id
)
select unit_id,link_id,unit From cte
with cte (link_id,unit_id,unit)
as
(
select link_id,unit_id,unit from parents where unit_id=4
union all
select b.link_id,b.unit_id,b.unit from cte as a inner join parents as b on a.link_id=b.unit_id
)
select unit_id,link_id,unit From cte
Read this