Endless Loop or No Output for Recursive SQL Query

I have the following table,

CREATE TABLE AGENT (
A_CODE int,
A_FNAME varchar(15),
S_CODE int /* supervisor */
);

INSERT INTO AGENT VALUES(1,'John',1);
INSERT INTO AGENT VALUES(2,'Nancy',1);
INSERT INTO AGENT VALUES(3,'Lottie',2);
INSERT INTO AGENT VALUES(4,'Jennie',1);
INSERT INTO AGENT VALUES(5,'Robert',3);
INSERT INTO AGENT VALUES(6,'Cary',1);
INSERT INTO AGENT VALUES(7,'Roberto',3);
INSERT INTO AGENT VALUES(8,'Elizabeth',1);
INSERT INTO AGENT VALUES(9,'Jack',2);
INSERT INTO AGENT VALUES(10,'Rose',4);
INSERT INTO AGENT VALUES(11,'Tom',3);
INSERT INTO AGENT VALUES(12,'Alan',2);
INSERT INTO AGENT VALUES(13,'Peter',3);
INSERT INTO AGENT VALUES(14,'Sherry',4);
INSERT INTO AGENT VALUES(15,'Howard',5);
INSERT INTO AGENT VALUES(16,'Barry',5);
INSERT INTO AGENT VALUES(17,'Jeanine',4);
INSERT INTO AGENT VALUES(18,'Andrew',4);
INSERT INTO AGENT VALUES(19,'Peter',4);
INSERT INTO AGENT VALUES(20,'Robert',1);
INSERT INTO AGENT VALUES(21,'Jennifer',3);

My goal is to create a supervisor view in which there is a 4th column that has the name of the supervisor that matches the S code. My query returns to me either nothing except the column headers or a never ending recursion that loops into the 100,000’s.

What I’ve written is

WITH SUPERVISOR AS
(
SELECT A_CODE, A_FNAME, S_CODE, CAST('' AS VARCHAR(50)) S_NAME FROM AGENT
UNION ALL
SELECT A.A_CODE,A.A_FNAME, A.S_CODE,CAST(S.A_FNAME AS VARCHAR(50)) S_NAME FROM AGENT AS A
INNER JOIN SUPERVISOR  AS S ON S.A_CODE=A.S_CODE
)
SELECT * FROM SUPERVISOR
option (maxrecursion 0);

Returns endlessly,

or

WITH SUPERVISOR AS
(
SELECT A_CODE, A_FNAME, S_CODE, CAST('' AS VARCHAR(50)) S_NAME FROM AGENT WHERE S_CODE is NULL
UNION ALL
 SELECT A.A_CODE,A.A_FNAME, A.S_CODE,CAST(S.A_FNAME AS VARCHAR(50)) S_NAME FROM AGENT AS A
INNER JOIN SUPERVISOR  AS S ON S.A_CODE=A.S_CODE
)
SELECT * FROM SUPERVISOR
option (maxrecursion 0);

I’ve been trying to read about this and figure it out on my own but at this point I’ve struggled for long enough that I think I need it explained to me as to why it’s not working or how wildly wrong I’m going about this.

Answer

Please try the following solution. It is using self-join approach with two aliases for the same table: e(mployee) and s(upervisor).

SQL

-- DDL and sample data population, start
DECLARE @AGENT TABLE (A_CODE INT PRIMARY KEY, A_FNAME varchar(15), S_CODE int /* supervisor */);
INSERT INTO @AGENT VALUES
(1,'John',1)
,(2,'Nancy',1)
,(3,'Lottie',2)
,(4,'Jennie',1)
,(5,'Robert',3)
,(6,'Cary',1)
,(7,'Roberto',3)
,(8,'Elizabeth',1)
,(9,'Jack',2)
,(10,'Rose',4)
,(11,'Tom',3)
,(12,'Alan',2)
,(13,'Peter',3)
,(14,'Sherry',4)
,(15,'Howard',5)
,(16,'Barry',5)
,(17,'Jeanine',4)
,(18,'Andrew',4)
,(19,'Peter',4)
,(20,'Robert',1)
,(21,'Jennifer',3);
-- DDL and sample data population, end

SELECT e.*, s.A_FNAME AS Supervisor
FROM @AGENT AS e INNER JOIN 
    @AGENT AS s ON s.A_CODE = e.S_CODE;

Output

+--------+-----------+--------+------------+
| A_CODE |  A_FNAME  | S_CODE | Supervisor |
+--------+-----------+--------+------------+
|      1 | John      |      1 | John       |
|      2 | Nancy     |      1 | John       |
|      3 | Lottie    |      2 | Nancy      |
|      4 | Jennie    |      1 | John       |
|      5 | Robert    |      3 | Lottie     |
|      6 | Cary      |      1 | John       |
|      7 | Roberto   |      3 | Lottie     |
|      8 | Elizabeth |      1 | John       |
|      9 | Jack      |      2 | Nancy      |
|     10 | Rose      |      4 | Jennie     |
|     11 | Tom       |      3 | Lottie     |
|     12 | Alan      |      2 | Nancy      |
|     13 | Peter     |      3 | Lottie     |
|     14 | Sherry    |      4 | Jennie     |
|     15 | Howard    |      5 | Robert     |
|     16 | Barry     |      5 | Robert     |
|     17 | Jeanine   |      4 | Jennie     |
|     18 | Andrew    |      4 | Jennie     |
|     19 | Peter     |      4 | Jennie     |
|     20 | Robert    |      1 | John       |
|     21 | Jennifer  |      3 | Lottie     |
+--------+-----------+--------+------------+