Hello all, I have the following query (after much searching and reading of Joe Celko and secretagents.com :-)
<!--- Builds the whole tree ---> <cfquery name="tree" datasource="#EAIDataSource#"> select Child.Content_ID, Child.Content_ID AS a_ID, Child.Content_Name_en, Count(Child.startbranch) AS lvl, Child.startbranch, Child.endbranch from Content_Tree AS Parent, Content_Tree AS Child where (Child.startbranch BETWEEN Parent.startbranch AND Parent.endbranch) group by Child.startbranch, Child.Content_ID, Child.Content_Name_en, Child.endbranch </cfquery> Followed by this code: <table cellpadding="0" cellspacing="0" style="width:196px;" align="center" border="0"> <cfoutput query="tree" group="Content_ID"> <cfset maxLvl = ArrayMax(ListToArray(ValueList(tree.lvl)))> <tr> <cfif lvl gt 1> #repeatstring('<td valign="top" style="width:10px;"> </td>',val(lvl - 2))# <td valign="top" style="width:10px;"><img src="images/general/flech.gif" width="10" height="10"></td> </cfif> <td class="tdp" valign="top" colspan="<cfif lvl eq 1>#val(maxLvl)#<cfelse>#val(maxLvl - (lvl - 1))#</cfif>"> <a href="index.cfm?Content_ID=#Content_ID#"> <cfif lvl eq 1><b></cfif>#highlight(Content_Name_en)#<cfif lvl eq 1></b></cfif></a> </td> </tr> </cfoutput> </table> Which outputs like this: Header (bold) > sub Header (bold) > sub >subsub >subsubsub >subsubsub >subsub >sub >subsub .. and so on. Now so far this is working exactly as planned, but I need to break up the listing into sub-groups on the Headers based on the selection on a top menu bar. so i appended the query thusly (based on Celko's recommendation in Chapter 29 of SQL for Smarties): <!--- Builds the sub tree ---> <cfquery name="subtree" datasource="#EAIDataSource#"> select Child.Content_ID, Child.Content_ID AS a_ID, Child.Content_Name_en, Count(Child.startbranch) AS lvl, Child.startbranch, Child.endbranch from Content_Tree AS Parent, Content_Tree AS Child where (Child.startbranch BETWEEN Parent.startbranch AND Parent.endbranch) AND Parent.Content_ID = #root.Content_ID# group by Child.startbranch, Child.Content_ID, Child.Content_Name_en, Child.endbranch </cfquery> Which should do this: sub (bold) sub (bold) >subsub >subsubsub >subsubsub >subsub sub (bold) >subsub But does this sub (bold) sub (bold) subsub (bold) subsubsub (bold) subsubsub (bold) subsub (bold) sub (bold) subsub (bold) My question is why? and what can I do to make it work? what am I missing? will ---- William H. Bowen Webmaster ALSTOM's T&D Energy Automation & Information Business "Your friendly neighborhood Webmaster!" [EMAIL PROTECTED] http://www.esca.com/ 425.739.3629 Voice 425.466.7016 Cell 425.739.3690 FAX ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists