Help with Select statement

Help me with the following query.
I have employee table as follows
EmpID (Number)
FullName (Text)
ReportTo(Number)
ReportTo field contain number from EmpID
Sample Data from the table
EmpIDFullNameReports To
1Nancy Davolio2
2Andrew Fuller
3Janet Leverling2
4Margaret Peacock2
5Steven Buchanan2
6Michael Suyama5
7Robert King5
8Laura Callahan2
9Anne Dodsworth5
10Andrew Leverling3
11Michael Miller5
12Robert Davolio3
13Nancy Suyama6
14Margaret King7
I would like to create a single query (T-SQL), when I pick an
employee ID, I would like to have the entire list of employees come
under that employee including that employee and all below him/her.
Example. If I pick ID = 5, should get the following list.
EmpIDFullNameReports To
5Steven Buchanan2
6Michael Suyama5
7Robert King5
9Anne Dodsworth5
11Michael Miller5
13Nancy Suyama6
14Margaret King7
The list above shows all staff who report to ID 5 directly, But I want very
who are reporting to staff reporting to staff report to ID 5 and below in the
hieratical organization.
How do I do that query?
Thanks
Harry
Harry J Nathan
see
http://www.mindsdoor.net/SQLTsql/Ret...Hierarchy.html
"Harry J Nathan" wrote:

> Help me with the following query.
> I have employee table as follows
> EmpID (Number)
> FullName (Text)
> ReportTo(Number)
> ReportTo field contain number from EmpID
> Sample Data from the table
> EmpIDFullNameReports To
> 1Nancy Davolio2
> 2Andrew Fuller
> 3Janet Leverling2
> 4Margaret Peacock2
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 8Laura Callahan2
> 9Anne Dodsworth5
> 10Andrew Leverling3
> 11Michael Miller5
> 12Robert Davolio3
> 13Nancy Suyama6
> 14Margaret King7
>
> I would like to create a single query (T-SQL), when I pick an
> employee ID, I would like to have the entire list of employees come
> under that employee including that employee and all below him/her.
> Example. If I pick ID = 5, should get the following list.
> EmpIDFullNameReports To
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 9Anne Dodsworth5
> 11Michael Miller5
> 13Nancy Suyama6
> 14Margaret King7
> The list above shows all staff who report to ID 5 directly, But I want very
> who are reporting to staff reporting to staff report to ID 5 and below in the
> hieratical organization.
> How do I do that query?
> Thanks
> Harry
> --
> Harry J Nathan