I've done this sort of tree stuff -- usually using a cross-reference table
which shows all children for all ancestors, i.e.
in the structure where Steve is Bob's boss is Sue's boss, the
cross-reference table contains one record which indicates each person
cross-referenced to themselves, steve to bob, bob to sue, and steve to sue
...
It's actually not horribly difficult if you build a trigger on the personel
table that calls a stored procedure something similar to this:
delete from personel_managers where personelid = @personelid;
insert into personel_managers ( personelid, managerid ) values (
@personelid, @managerid );
insert into personel_managers ( personelid, managerid )
select @personelid, pm2.managerid from personel_managers pm2
where pm2.personelid =
(select managerid from personel where personelid = @personelid)
This will create your cross-reference table, then you can draw all the
people in a given tree fairly simply:
select p.* from personel p
inner join personel_managers pm on ( pm.personelid = p.personelid )
where pm.managerid = @managerid;
A recursive user defined function or custom tag should be able to display
them in the proper order -- and/or if you get creative, you can even add the
distance of separation between manager and managed to the cross-reference
table...
I'm told that Joe Celko talks about this method in a book called SQL For
Smarties although I can't vouche for the book personally...
Isaac
Certified Advanced ColdFusion 5 Developer
www.turnkey.to
954-776-0046
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
