SQL recursive level 1 supervisee staff

Question

I am stack again. I want to get all the supervisors who supervise staff less than 1.

In my result I miss one supervisor so I think my Query is wrong.

  SELECT   c.EmpID,c.name,c.gender,c.salary,COUNT(*) AS superviseeStaff_Count
  FROM     staff c  INNER JOIN staff u ON c.EmpID = u.supervisorFk 
           And  c.position='Supervisor' 
  GROUP BY c.EmpID,c.name,c.gender, c.salary
  HAVING   COUNT(*) = 1

Result:
38 Anna-Isabell Green F 32000.0 1
42 Winston Hughes M 22000.0 1
61 Sally Thatcher F 36000.0 1

I miss Supervisor with empID 48.

I tried with With clause and same but no effect on results.

Staff table looks like: EmpID,name,gender,salary,supervisorFk(Foreignkey of supervisor),BranchFK,position

--Data:

delete from Staff;

    --  data for table Staff   
    --  ---------------------

    -- 67 Employees 


    insert into Staff values ( 1, 'Robert King'      ,'M', 143000.0,NULL, 'B002', 'Director');
    insert into Staff values ( 2, 'Rafael McDonalds' ,'M',  72000.0, 1,'B001', 'Manager');
    insert into Staff values ( 3, 'John White'       ,'M',  60000.0, 1,'B003', 'Manager');
    insert into Staff values ( 4, 'Susan Brand'      ,'F',  55000.0, 1,'B004', 'Manager');
    insert into Staff values ( 5, 'Cathy Brown'      ,'F',  32000.0, 2,'B001', 'Supervisor');
    insert into Staff values ( 6, 'Claire Dujeune'   ,'F',  79000.0, 1,'B010', 'Manager');
    insert into Staff values ( 7, 'Patty Summer'     ,'F',  23000.0, 2,'B001', 'Supervisor');
    insert into Staff values ( 8, 'Mary Fleming'     ,'F',  43000.0, 1,'B011', 'Manager');
    insert into Staff values (10, 'Anne Beech'       ,'F',  26000.0, 5,'B001', 'Assistant');
    insert into Staff values (12, 'Paul Coplien'     ,'M',  27000.0, 3,'B003', 'Supervisor');
    insert into Staff values (13, 'David Ford'       ,'M',  41000.0, 1,'B012', 'Manager');
    insert into Staff values (14, 'Mary Howe'        ,'F',  55000.0, 1,'B005', 'Manager');
    insert into Staff values (15, 'Julie Lee'        ,'F',  34000.0, 3,'B003', 'Supervisor');
    insert into Staff values (16, 'Aaron Young'      ,'M',  74000.0, 1,'B013', 'Manager');
    insert into Staff values (17, 'Albert Thomson'   ,'M',  25000.0,13,'B012', 'Supervisor');
    insert into Staff values (21, 'Alexander Reynolds','M', 37000.0, 5,'B001', 'Assistant');
    insert into Staff values (18, 'Christine McDonalds','F',38000.0, 4,'B004', 'Supervisor');
    insert into Staff values (19, 'Elisa Pinkerton'  ,'F',  36000.0,16,'B013', 'Supervisor');
    insert into Staff values (20, 'Eric Montgomery'  ,'M',  33000.0, 7,'B001', 'Assistant');
    insert into Staff values (22, 'Edward Robinson'  ,'M',  28000.0, 7,'B001', 'Assistant');
    insert into Staff values (23, 'Jesse Owens'      ,'M',  34000.0, 4,'B004', 'Supervisor');
    insert into Staff values (25, 'Johnatan Hunter'  ,'M',  45000.0, 1,'B014', 'Manager');
    insert into Staff values (26, 'Lenita Kennedy'   ,'F',  56000.0, 1,'B009', 'Manager');
    insert into Staff values (27, 'Lisa Miller'      ,'F',  34000.0,25,'B014', 'Supervisor');
    insert into Staff values (28, 'Lilly Jennings'   ,'F',  36000.0,14,'B005', 'Supervisor');
    insert into Staff values (29, 'Rafaela Johnson'  ,'F',  23000.0,12,'B003', 'Assistant');
    insert into Staff values (32, 'Harry Anderson'   ,'M',  40000.0,12,'B003', 'Assistant');
    insert into Staff values (33, 'George Bailey'    ,'M',  73000.0, 1,'B006', 'Manager');
    insert into Staff values (34, 'Salomon Beckett'  ,'M',  46000.0, 1,'B008', 'Manager');
    insert into Staff values (35, 'Susan Armstrong'  ,'F',  28000.0,15,'B003', 'Assistant');
    insert into Staff values (36, 'Rosa Hemingway'   ,'F',  30000.0,15,'B003', 'Assistant');
    insert into Staff values (37, 'Martha McDonalds' ,'F',  31000.0,15,'B003', 'Assistant');
    insert into Staff values (38, 'Anna-Isabell Green','F', 32000.0,33,'B006', 'Supervisor');
    insert into Staff values (39, 'Tina Hall-Becker' ,'F',  34000.0,18,'B004', 'Assistant');
    insert into Staff values (40, 'Thomas Harrison'  ,'M',  42000.0, 1,'B007', 'Manager');
    insert into Staff values (42, 'Winston Hughes'   ,'M',  22000.0,40,'B007', 'Supervisor');
    insert into Staff values (44, 'Walter Jefferson' ,'M',  23000.0,18,'B004', 'Assistant');
    insert into Staff values (45, 'Zara Newton'      ,'F',  24000.0,23,'B004', 'Assistant');
    insert into Staff values (46, 'Nina McDonalds'   ,'F',  25000.0,23,'B004', 'Assistant');
    insert into Staff values (48, 'Carol Moore'      ,'M',  27000.0,40,'B007', 'Supervisor');
    insert into Staff values (51, 'Alexander Porter' ,'M',  29000.0,17,'B012', 'Assistant');
    insert into Staff values (52, 'Maria Quasimodo'  ,'M',  30000.0,17,'B012', 'Assistant');
    insert into Staff values (53, 'Bertrand Russel'  ,'M',  31000.0,34,'B008', 'Supervisor');
    insert into Staff values (54, 'Ashley Parker'    ,'M',  25500.0,28,'B005', 'Assistant');
    insert into Staff values (55, 'John Stuart'      ,'M',  23500.0,28,'B005', 'Assistant');
    insert into Staff values (56, 'Ruth Sanderss'    ,'F',  27700.0,19,'B013', 'Assistant');
    insert into Staff values (57, 'Rafael Smith'     ,'M',  32000.0,19,'B013', 'Assistant');
    insert into Staff values (58, 'Viola Rutherford' ,'F',  21000.0,27,'B014', 'Assistant');
    insert into Staff values (59, 'Sammy Churchill'  ,'M',  22000.0,27,'B014', 'Assistant');
    insert into Staff values (60, 'Miriam Thorne'    ,'F',  26000.0,27,'B014', 'Assistant');
    insert into Staff values (61, 'Sally Thatcher'   ,'F',  36000.0,34,'B008', 'Supervisor');
    insert into Staff values (62, 'Larry Escott'     ,'M',  33000.0,26,'B009', 'Supervisor');
    insert into Staff values (63, 'William Spencer'  ,'M',  32000.0, 6,'B010', 'Supervisor');
    insert into Staff values (64, 'Diana Ashley-Bell','F',  38000.0, 8,'B011', 'Supervisor');
    insert into Staff values (65, 'Audrey Thorne'    ,'F',  25000.0,62,'B009', 'Assistant');
    insert into Staff values (66, 'Paula Burns'      ,'F',  24000.0,62,'B002', 'Assistant');
    insert into Staff values (67, 'Amanda Wallis'    ,'F',  23000.0,38,'B006', 'Assistant');
    insert into Staff values (68, 'Patty Stokes'     ,'F',  22000.0,53,'B008', 'Assistant');
    insert into Staff values (69, 'Holly Fields'     ,'F',  21500.0,53,'B008', 'Assistant');
    insert into Staff values (70, 'Martha McCulloch' ,'F',  26000.0,61,'B008', 'Assistant');
    insert into Staff values (71, 'Maurin Best'      ,'F',  22500.0,42,'B007', 'Assistant');
    insert into Staff values (72, 'Martha McDonalds' ,'F',  23500.0,27,'B014', 'Assistant');
    insert into Staff values (73, 'Barrigan' ,NULL,  23500.0,27,'B013', 'Assistant');
    insert into Staff values (47, 'Naomi Campell'    ,'F',  26000.0,63,'B010', 'Assistant');
    insert into Staff values (49, 'Tony McDonalds'   ,'M',  28000.0,63,'B010', 'Assistant');
    insert into Staff values (50, 'Margret McElroy'  ,'F',  29000.0,64,'B011', 'Assistant');
    insert into Staff values ( 9, 'Carl Maier'       ,'M',  31000.0,64,'B011', 'Assistant');

I am trying this but not working:

WITH Tree
AS (
SELECT
    EmpID,
    name,
    gender,
    salary,SupervisorFK,
    1 AS Level
   FROM staff
WHERE position='Supervisor'

UNION ALL

SELECT 
    st.EmpID,
    st.name,
    st.gender,
    st.salary,st.SupervisorFK,
    level + 1
FROM Staff AS st
    JOIN Tree uh ON uh.EmpID = st.SupervisorFK    
)

SELECT * FROM Tree

Answer 1

This is pretty simple actually. In your source data, there are no employees with a supervisorFk value of 48, so Supervisor #48 (Carol Moore) has no employees. Since you're doing an INNER JOIN based on that foreign key, that record is never in the initial set. You don't even make it to the HAVING clause of COUNT(*)=1 - the user isn't there to be counted.

Change that to a LEFT JOIN and make your COUNT(*)<=1, or add a record that has a supervisorFk of 48 and then you'll see the user.