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
精彩评论