Recursive only 2 level records using common sql

I have an Employee table, it's a self-referencing table, with managerId refers to primary key empID. I want to find 2 level records by a given empId.

For example: if given empId=5, if empId=5 has children records, display them, as well as the children in children records. The database is SQL Server 2005.

update: I wanna a full project开发者_开发知识库ion out

---

This should get you started. Not sure what you mean by "full projection out". If you mean every child of every child, then you'll need a loop. But if you know you just want to go down 2 levels, this example will work:

SELECT
   t1.empID as Parent_ID,
   t2.empID as Child_ID,
   t3.empID as Grandchild_ID
FROM 
   employee t1
   LEFT JOIN employee t2 ON t1.empID = t2.managerID
   LEFT JOIN employee t3 ON t2.empID = t3.managerID
WHERE t1.empID = @given_empID