SQL View

[DKL01]

Hi,

I have a table with following columns and rows :

EMP_ID DESIGNATION SUPERVISOR_ID
1 Programmer(East) 7
3 Programmer(West) 11
4 Global Manager(East) 4
6 Sr. Proj Manager(East) 4
7 Project Manager(East) 6
10 Global Manager(West) 10
11 Project Manager(West) 12
12 Sr. Proj Manager(West) 10

I need to create a view based on above table. It should display

EMP_ID IMMEDIATE_SUPERVISOR TOP_SUPERVISOR
1 7 4
3 11 10
4 4 4
6 4 4
7 6 4
10 10 10
11 12 10
12 10 10

I really appreciate your suggestions/logic to write this view.

Thanks

 

[wushutwist]

As far as I can see it can't be done with a simple sql statement, there is too much logic that is needed so a view would be out of the question since it can only use select statements. I would write a stored procedure to do this and then create another row to store the TOP_SUPERVISOR value and populate it with the stored procedure. Then create a trigger to run whenever a record is added to populate the TOP_SUPERVISOR column.

Wushutwist

 

[TomSark]

Look at the books-online article entitled expanding networks and the article entitled expanding heirarchies.
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com