title: How to develop a recursive CTE in T-SQL?
body: <p>I am new to recursive CTEs. I am trying to develop a CTE which will return all of the employees under each manager name. So I have two tables: <code>people_rv</code> and <code>staff_rv</code></p>
  
  <p>People_rv table contains all of the people, both managers and employees. <code>Staff_rv</code> only contains manager information.  Uniqueidentifier staff values are stored in <code>Staff_rv</code>.  Uniqueidentifier employee values are stored in <code>people_rv</code>.  <code>People_rv</code> contains varchar first and last name values for both managers and employees.</p>
  
  <p>But when I run the following CTE I get an error:</p>
  
  <pre><code>WITH
  cteStaff (ClientID, FirstName, LastName, SupervisorID, EmpLevel)
  AS
  (
      SELECT p.people_id, p.first_name, p.last_name, s.supervisor_id,1
      FROM people_rv p JOIN staff_rv s on s.people_id = p.people_id
      WHERE s.supervisor_id = '95E16819-8C3A-4098-9430-08F0E3B764E1' 
      UNION ALL
      SELECT p2.people_id, p2.first_name, p2.last_name, s2.supervisor_id, r.EmpLevel + 1
      FROM people_rv p2 JOIN staff_rv s2 on s2.people_id = p2.people_id
      INNER JOIN cteStaff r on s2.staff_id = r.ClientID
  )
  SELECT
      FirstName + ' ' + LastName AS FullName, 
      EmpLevel,
      (SELECT first_name + ' ' + last_name FROM people_rv p join staff_rv s on s.people_id = p.people_id 
      WHERE s.staff_id = cteStaff.SupervisorID) AS Manager
  FROM cteStaff
  OPTION (MAXRECURSION 0);
  </code></pre>
  
  <p>My output is:</p>
  
  <pre><code>Barbara G   1   Melanie K
  Dawn P  1   Melanie K
  Garrett M   1   Melanie K
  Stephanie P 1   Melanie K
  Amanda F    1   Melanie K
  Amanda T    1   Melanie K
  Stephanie G 1   Melanie K
  Carlos H    1   Melanie K
  </code></pre>
  
  <p>So it is not iterating any more than the first level.  Why not?
  Melanie is the top most supervisor, but each of the persons in the leftmost column are also supervisors.  So this query should also return level 2.</p>