title: Recursive CTE-Find all Employees Below Manager
body: <p>I created a sample fiddle for this <a href="http://sqlfiddle.com/#!3/43265/1" rel="nofollow">SQLFIDDLE</a></p>
  
  <pre><code>CREATE TABLE [dbo].[Users](
      [userId] [int] ,
      [userName] [varchar](50) ,
      [managerId] [int] ,
     )
  
  INSERT INTO dbo.Users
      ([userId], [userName], [managerId])
  VALUES
  (1,'Darry',NULL),
  (2,'Cono',1),
  (3,'Abros',2),
  (4,'Natesh',1),
  (5,'Ani',3),
  (6,'Raju',5),
  (7,'Pinky',5),
  (8,'Miya',4)
  </code></pre>
  
  <p>My requirement is like displaying all employees hierarchy below that particular manager</p>
  
  <p>Here is what i tried</p>
  
  <pre><code>WITH UserCTE AS (
    SELECT userId, userName, managerId, 0 AS EmpLevel
    FROM Users where managerId IS NULL
  
    UNION ALL
  
    SELECT usr.userId, usr.userName, usr.managerId, mgr.[EmpLevel]+1
    FROM Users AS usr
      INNER JOIN UserCTE AS mgr
        ON usr.managerId = mgr.userId where usr.managerId IS NOT NULL
  )
  SELECT * 
    FROM UserCTE AS u where u.ManagerId=3
    ORDER BY EmpLevel;
  </code></pre>
  
  <p><strong>Output :</strong></p>
  
  <pre><code>userName
  --------
      Ani
  </code></pre>
  
  <p>The output that i am expecting is, if i give a ManagerId 3,then following employees should be shown</p>
  
  <pre><code>1.Abros
  2.Ani
  3.Raju
  4.Pinky
  </code></pre>
  
  <p>Can anyone help on this</p>