I'm implementing Joe Celko's nested set functionality to store an employee
tree...the wrinkle is that I have several trees (representing different
companies) stored in the same table...with a companyID identifier.
Everything works great except for the count(*) included with each
row....it's not coming up the way it should, and I'm not even sure exactly
how it's messing up - some people who are on level 1 are listed at level 7,
some at level 2, etc. I can't figure the pattern.
(Here's my query)
<cfquery datasource=#dsn# name=get>
select child.userID, child.username, count(*) as thislevel,
child.leftno,
child.rightno
from salesroles as parent, salesroles as child
where child.companyID = #companyID# and child.leftno between
parent.leftno
and parent.rightno
group by child.leftno, child.userID, child.username, child.rightno
</cfquery>
(DB is currently in Access, but will move to SQL 2000 within a week -
assuming I get it to work - so any wrinkles that differ between them would
also be a great help.)
I'm lost....any help would be greatly appreciated!
D
*************
Diana Nichols
Webmistress
http://www.lavenderthreads.com
770.434.7374
"One man's magic is another man's engineering." ---Lazarus Long
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
