nor selecting all your columns, but those should be straightforward.
<cfquery name="listcategories" datasource="#request.dsn1#">
SELECT cat.CatID AS itemID,
sub.CatID AS subitemID,
subsub.CatID AS subsubitemID,
subsubsub.CatID AS subsubsubitemID
FROM Categories_internal cat
LEFT OUTER JOIN Categories_internal sub ON cat.CatID = sub.parentID
LEFT OUTER JOIN Categories_internal subsub ON sub.CatID =
subsub.parentID
LEFT OUTER JOIN Categories_internal subsubsub ON subsub.CatID =
subsubsub.parentID
WHERE cat.catlevel = 0
</cfquery>
<cfoutput query="listcategories" group="itemID">
blah
<cfoutput group="subitemID">
blah
<cfoutput group="subsubitemID">
blah
<cfoutput>
blah
</cfoutput>
</cfoutput>
</cfoutput>
</cfoutput>
Cheers,
barneyb
> -----Original Message-----
> From: Robert Everland III [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, April 14, 2004 11:39 AM
> To: CF-Talk
> Subject: Re: Issue with Loops and queries
>
> Here you go, I've been thinking of a better way of doing it
> and haven't come up with anything.
>
> <cfset request.dsn1 = "fidelityifs">
> <cfset request.self = "index.cfm">
> <cfset fusebox.targetcircuit = "test">
> <cfquery name="listcategories" datasource="#request.dsn1#"
> dbtype="ODBC">
> Select CatID as itemid ,
> Category as description,
> ParentId as ParentItemID,
> catlevel,
> catid,
> category,
> parentid
> From Categories_internal
> where catlevel = 0
> order by Orderby desc
> </cfquery>
> <cfoutput>
> <!---
> <table width="100%" border="0" cellspacing="0" cellpadding="0">
> <tr>
> <td bgcolor="###attributes.sectioncolor#"
> class="LeftMenuHeader">
> <img src=""
> width="182" height="5" alt=""><br>
> <img src=""
> width="10" height="1" alt=""><a
> href=""> nt&catid=#attributes.sectionid#" style="color:white; text->
decoration:none;">#attributes.sectionname#</a><br>
> <img src=""
> width="182" height="5" alt=""><br>
> </td>
> </tr>
> </table> --->
> <script language="_javascript_">
> // You can find instructions for this file at http://www.treeview.net
>
> //Environment variables are usually set at the top of this file.
> USETEXTLINKS = 1
> STARTALLOPEN = 0
> USEFRAMES = 0
> USEICONS = 0
> WRAPTEXT = 1
> PERSERVESTATE = 1
> ICONPATH = "js/tree/"
>
> foldersTree = gFld("", "")
> foldersTree.treeID = "viewcontent"
>
> <cfoutput query="listcategories">
> aux1 = insFld(foldersTree, gFld("#category#",
> "#request.self#?fuseaction=#fusebox.targetcircuit#.content&cat
> id=#catid#"))
> <cfquery name="listsubcategories"
> datasource="#request.dsn1#" dbtype="ODBC">
> Select Categories_internal.CatID as itemid ,
>
> Categories_internal.Category as description,
>
> Categories_internal.ParentId as ParentItemID,
> Categories_internal.catlevel,
> Categories_internal.catid,
> Categories_internal.category,
> Categories_internal.parentid
> From Categories_internal inner join
> articles on (Categories_internal.catid = articles.catid_internal)
> Where Categories_internal.parentID =
> #listcategories.catid#
> and #now()# between
> articles.starttime and articles.endtime
> </cfquery>
> <cfoutput query="listsubcategories">
> aux2 = insFld(aux1, gFld("#category#",
> "#request.self#?fuseaction=#fusebox.targetcircuit#.content&cat
> id=#catid#"))
> <cfquery name="listsubcategories2"
> datasource="#request.dsn1#" dbtype="ODBC">
> Select
> Categories_internal.CatID as itemid ,
>
> Categories_internal.Category as description,
>
> Categories_internal.ParentId as ParentItemID,
>
> Categories_internal.catlevel,
>
> Categories_internal.catid,
>
> Categories_internal.category,
>
> Categories_internal.parentid
> From Categories_internal
> inner join articles on (Categories_internal.catid =
> articles.catid_internal)
> Where
> Categories_internal.parentID = #listsubcategories.catid#
> and #now()# between
> articles.starttime and articles.endtime
> </cfquery>
> <cfoutput query="listsubcategories2">
> aux3 = insFld(aux2, gFld("#category#",
> "#request.self#?fuseaction=#fusebox.targetcircuit#.content&cat
> id=#catid#"))
> <cfquery
> name="listsubcategories3" datasource="#request.dsn1#" dbtype="ODBC">
> Select
> Categories_internal.CatID as itemid ,
>
> Categories_internal.Category as description,
>
> Categories_internal.ParentId as ParentItemID,
>
> Categories_internal.catlevel,
>
> Categories_internal.catid,
>
> Categories_internal.category,
>
> Categories_internal.parentid
> From
> Categories_internal inner join articles on
> (Categories_internal.catid = articles.catid_internal)
> Where
> Categories_internal.parentID = #listsubcategories.catid#
> and #now()#
> between articles.starttime and articles.endtime
> </cfquery>
> <cfoutput query="listsubcategories3">
> test
> </cfoutput>
> </cfoutput>
> </cfoutput>
> </cfoutput>
> </script>
> <script>initializeDocument()</script>
> </cfoutput>
>
>
> >yah, can you show the query?
> >
> >we can probably dress it up real nice...
> >
> >tw
> >
> >-----Original Message-----
> >From: Philip Arnold [mailto:[EMAIL PROTECTED]
> >Sent: Wednesday, April 14, 2004 2:19 PM
> >To: CF-Talk
> >Subject: RE: Issue with Loops and queries
> >
> >
> >Are these queries from the same datasource?
> >
> >If so, do it in one query with joins (probably left outer join)
> >
> >You'll save processing on the CF end, and will end up with
> one CFOUTPUT
> >GROUP= instead
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
