Just a good night sleep is all I needed...
<cfquery name="tree" datasource="#EAIDataSource#">
select CT2.Content_ID, CT2.Content_ID AS a_ID, CT2.Content_Name_en,
Count(CT2.startbranch) as lvl, CT2.startbranch, CT2.endbranch
from Content_Tree AS CT1, Content_Tree AS CT2, Content_Tree AS CT3
where CT1.Content_ID = #child2root.Content_ID#
AND CT2.startbranch BETWEEN CT1.startbranch AND CT1.endbranch
AND CT2.startbranch BETWEEN CT3.startbranch AND CT3.endbranch
AND CT3.startbranch BETWEEN CT1.startbranch AND CT1.endbranch
group by CT2.startbranch, CT2.Content_ID, CT2.Content_Name_en,
CT2.endbranch
order by CT2.startbranch
</cfquery>
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
----- Original Message -----
From: "William H. Bowen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, July 24, 2002 6:19 PM
Subject: Nested Set question
> 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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
