Single Table/Multiple Object Hierarchy

Phlar posted on Wednesday, June 04, 2008

I am sure that I'm not the first individual to be in this situation.  We have a single table Employees which contains the following:

Employee_Id     uniqueidentifier (integer)

First_Name       varchar(50)

Last_Name       varchar(50)

Supervisor_Id   integer

We have two criteria's we need to fulfill.  The first is to produce an Org structure starting from the CEO (Supervisor_id = NULL) all the way down.  The second is retrieve a particular Employee and his/her supervisor.

Is there a simple way to reduce continuous round trips to the server in order to build the object model?  Originally I was contemplating utilizing CTE as:

with usertree(id, first, last, supervisor)

as (Select Employee_Id, First_Name, Last_Name, Supervisor_Id

     From Employee

     Where Supervisor_id is NULL

     Union All

     Select e.Employee_id, e.First_Name, e.Last_Name, e.Supervisor_Id

     From Employee e

              Inner Join usertree ut on ut.Id = e.Employee_Id)

Select id, first, last, supervisor

From usertree

Order by superviosr

 

this does return all the rows but doesn't exactly produce the object model I was hoping for since we are only return one dataset and not multiple which eliminates the ability to use Empl : BusinessBase<Empl> and EmplList : BusinesBaseList <EmplList>

Any ideas?

 

ajj3085 replied on Wednesday, June 04, 2008

Why can't you still have Empl and EmplList?  The root Empl would execute your CTE, and since it knows what employee you're examing, can filter out the proper record for it to populate itself.  You can give the remaining rows to the EmplList to populate itself.