Recursive SQL Query

[chintapalli]

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. Admin
2. EBSM
3. Broker
4. Assistant

These 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.

 

[petersJazz]

Some DBMS suports reading a tree structure.

In your case you can use self-join to get the requested result (if I understand the question right):

select a.id,c.id
from user a, user b, user c
where a.usertype = 'Manager'
and b.usertype = 'Chef'
and c.usertype = 'Assistant'
and b.managerid = a.id
and c.managerid = b.id
and a.username = 'Coltrane';