John
Assuming your tables are similar to
Code:
Offices
officeId
parentOfficeId
name
officeType
Employees
empId
name
officeId
To pull all users under any level of office
Code:
-- set up working table to hold offices
select officeId
into #TempOffices
from Offices
where officeId = @office
-- loop adding child offices
while( @@rowcount > 0 )
begin
insert #TempOffices
select o.officeId
from Offices o,
#TempOffices t
where o.parentOfficeId = t.officeId
and not exists(
select 1
from #TempOffices t2
where t2.officeId = o.officeId )
end
-- extract employees under these offices
select e.name
from #TempOffices t,
Employees e
where e.officeId = t.officeId
-- clean up
drop table #TempOffices
It's not difficult to use a hierarchy like this - the above will work for any level of office but it does assume an office has just one parent (you'd need to add a separate relationship table if this isn't the case). The code is sybase but I don't have a database at hand to try it out on. The loop adds new offices under the offices already gathered so it may loop 1 - 3 times depending on what your starting office is.
If you had separate tables holding the employees in each office then the difficulty is then deciding which tables to include. This put's the complexity into the code (deciding which tables to use) rather than once in the database.
Code:
Account Admin A
->Company A
->->Employees 1..N
->Company B
->->Employee 1..N
Small point but I assume the above example doesn't mean two employees could have empId 1? - if it does then you want to get rid of that straight away.
Mike
dBforums
>
User Management Design Question
01-12-08, 10:11
Linear Mode
