This alternative offers OP the option to specify which companyid to to start at (also if companyid is a "subcompany"):declare @company table ( companyid int ,parentcompanyid int);insert into @company (companyid,parentcompanyid) values (1,null) ,(2,null) ,(3,null) ,(4,1) ,(5,4) ,(6,2) ,(7,5) ,(8,6) ,(9,3) ,(10,7);with _companytree as (select a.companyid as rootcompanyid ,a.companyid ,b.companyid as nextcompanyid ,0 as level from @company as a left outer join @company as b on b.parentcompanyid=a.companyid where a.parentcompanyid is null or b.companyid is null union all select b.rootcompanyid ,a.companyid ,c.companyid as nextcompanyid ,b.level+1 as level from @company as a inner join _companytree as b on b.nextcompanyid=a.companyid inner join @company as c on c.parentcompanyid=a.companyid ) ,companytree as (select * from _companytree where nextcompanyid is not null union all select b.rootcompanyid ,a.companyid ,a.nextcompanyid ,b.level+1 as level from _companytree as a inner join _companytree as b on b.nextcompanyid=a.companyid where a.nextcompanyid is null )select c.* from companytree as a inner join companytree as b on b.rootcompanyid=a.rootcompanyid and b.level>=a.level inner join @company as c on c.companyid=b.companyid where a.companyid=4;
All one has to do, is change the line marked in red, to receive the company tree from that companyid.