Recursion according to the first layer

[#1]

CREATE TABLE ORG( 
  EMPID INTEGER NOT NULL, 
  EMPNAME VARCHAR(128) NOT NULL, 
  MGRID INTEGER NOT NULL);

 INSERT INTO ORG VALUES(1, 'Jack', 0); 
 INSERT INTO ORG VALUES(2, 'Mary', 1); 
 INSERT INTO ORG VALUES(3, 'Tom', 1); 
 INSERT INTO ORG VALUES(4, 'Ben', 2); 
 INSERT INTO ORG VALUES(5, 'John', 3); 
 INSERT INTO ORG VALUES(6, 'Emily', 3); 
 INSERT INTO ORG VALUES(7, 'Kate', 3); 
 INSERT INTO ORG VALUES(8, 'Mark', 6);

RESULT:Each branch of want EMPID = 1

MGRID   EMPNAME   EMPID
TOM       JOHN      3
TOM      EMILY      3
TOM      KATE       4
TOM      mark       6
MARK     BEN          2

[ximen]

 with recursive ps(empid,empname,Parent_id) as(
select empid,empname,Parent_id=convert(varchar(100),empname) from org where mgrid=1
union all
select org.empid,org.empname,Parent_id=convert(varchar(100),ps.parent_id) from ps,org where org.mgrid=ps.empid)
select * from ps

 2,Mary,Mary
4,Ben,Mary
3,Tom,Tom
7,Kate,Tom
6,Emily,Tom
5,John,Tom
8,Mark,Tom

[VolkerBarth]

Just in order to understand:

Is that the query with your desired result or do you want to get further advice?

(I would think that the managers of empid 2, 3 and 8 are not correct when compared to your question, but that's just another wild guess here...)