RE: Help with Select statement

From: Nigel Rivett (sqlnr_at_hotmail.com)
Date: 01/20/05 

Date: Wed, 19 Jan 2005 18:01:03 -0800

see
http://www.mindsdoor.net/SQLTsql/RetrieveTreeHierarchy.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
>
> 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