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.
Advertisement
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 | +--------+-----------+--------+------------+