Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-28 : 08:39:23
|
| Harish Chintapalli writes "Hi,Can we write a recursive SQL query?I have a User table. It has ID (primary key), UserType and ManagerID apart from many other fields. I have 4 user types.1. Admin2. EBSM3. Broker4. AssistantThese user types follow hierarchy i.e. an Assistant belongs to a Broker, a Broker belongs to an EBSM.I would like to know if its possible to write one single query to fetch all Assistants for a given user. i.e. i. if I pass a Broker's UserID the query should return me Assistants that belong to this Broker.ii. if I pass an EBSM's UserID the query should return me Assistants that belong to each Broker that belong to this EBSM.iii. if I pass Admin's UserID the query should return me all Assistants.I know I can write an SP to accomplish this. But is there an easier and shorter way?Thanks.Harish." |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-28 : 09:03:43
|
| This type of hierarchy model is called an adjacency model. You can climb up and down the three by using 'self-joins'. Meaning: users u1 inner join users u2 on u1.id = u2.managerid . . . to climb more levels you need more self-joins. (Note: This is not 'recursive' in the standard procedural sense . . .)Is their an easier way? Maybe, I would suggest reading this article and the Celko articles linked from there . . .<O> |
 |
|
|
|
|
|
Recursive SQL Query