Help with Select statement

From: Harry J Nathan (HarryJNathan_at_discussions.microsoft.com)
Date: 12/13/04 

Date: Mon, 13 Dec 2004 06:59:08 -0800

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

EmpID FullName Reports To
1 Nancy Davolio 2
2 Andrew Fuller
3 Janet Leverling 2
4 Margaret Pea*** 2
5 Steven Buchanan 2
6 Michael Suyama 5
7 Robert King 5
8 Laura Callahan 2
9 Anne Dodsworth 5
10 Andrew Leverling 3
11 Michael Miller 5
12 Robert Davolio 3
13 Nancy Suyama 6
14 Margaret King 7

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.

EmpID FullName Reports To
5 Steven Buchanan 2
6 Michael Suyama 5
7 Robert King 5
9 Anne Dodsworth 5
11 Michael Miller 5
13 Nancy Suyama 6
14 Margaret King 7

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