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?

Here is a similar question, it might help you write a recursive query to solve your problem: [SQL Server recursive query](https://stackoverflow.com/questions/3916597/sql-server-recursive-query) — Jacob H, Jan 17 '20 at 15:48
ANSI-92 style joins have been around now for close to 30 years. Time to upgrade your join syntax. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — Sean Lange, Jan 17 '20 at 15:49
Which version of sql server are you using? You will need a recursive cte for this regardless. But depending on the version you can use CONCAT of you will need to utilize STUFF with XML. — Sean Lange, Jan 17 '20 at 15:53
If you know the maximum level of depth for your structure you can avoid using CTEs and recursive stuff. Just use left joins. — Elastep, Jan 17 '20 at 16:01

score 1 · Answer 1 · answered Jan 17 '20 at 16:21

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);

Self join in SQL Server

1 Answers1