Self join in SQL Server

Question

I have a two tables in SQL Server:

Employee (Parent):

EMPID | EMPNAME | ROLE      | DEPTID
1     | John    | Manager   | M1
2     | George  | Employee  | E1
3     | Alex    | Intern    | I1
4     | Bob     | Employee  | E2

REL (It holds Emp to Emp relationship):

FROM_EID | TO_EID
1        | 2
2        | 3

Expected output:

RELATED_ID
M1-E1-I1

I am using the below query to fetch the details of EMPID 1, 2 and 3 so that I can concat the DEPTIDs later:

select * from REL rel1, REL rel2, REL rel3
where rel1.FROM_EID = rel2.TO_EID 
and rel2.FROM_EID  = rel3.TO_EID;

But I am not getting the details for EMPID #1 here. How can I achieve this?

Answer 1

Here you go:

with 
x as (
  select
    empid as startid, 0 as level, 
    empid, cast(concat('', deptid) as varchar(255)) as chain
  from employee where empid = 1
union all
  select
    x.startid, x.level + 1, 
    e.empid, cast(concat(chain, '-', e.deptid) as varchar(255))
  from x
  join rel r on r.from_eid = x.empid
  join employee e on e.empid = r.to_eid
),
y as (
  select startid, max(level) as max_level from x group by startid
)
select x.chain
from x
join y on x.startid = y.startid and x.level = y.max_level

Result:

chain
--------
M1-E1-I1

For reference, the data script I used is:

create table employee (
  empid int,
  empname varchar(10),
  role varchar(10),
  deptid varchar(10)
);  

create table rel (
  from_eid int,
  to_eid int
);

insert into employee (empid, empname, role, deptid) values 
  (1, 'John', 'Manager', 'M1'), (2, 'George', 'Employee', 'E1'),
  (3, 'Alex', 'Intern', 'I1'),  (4, 'Bob', 'Employee', 'E2');

insert into rel (from_eid, to_eid) values
  (1, 2), (2, 3);