Help with the script

[Ebad Mobaligh]

I want to write one SQL query which returns all their bottom hierarchy department and employee count of each department. I don't want to use any cursor.

For Example:
If I select department D1 then it should show all department and employee count for that department, and If I select D4 then It should show D4, D6 and D7 and the number of employees in each of those departments.

Department Table
-----------------
DeptId ParentDeptId
D1
D2 D1
D3 D1
D4 D3
D5 D3
D6 D4
D7 D4


Employee table
-----------------------------
EmpId EmpName DeptId
1 E1 D1
2 E2 D2
3 E3 D3
4 E4 D3
5 E5 D4
6 E6 D5
7 E7 D6
8 E8 D7
9 E9 D7