In an Intranet application I'm working on, I have a main page that presents
summary information in a long table. I have one SQL query that gets the bulk
of the information (3 to 6 columns). However, in one of the columns, I
provide a list of which departments relate to that record.
Currently, I loop over the main query and make a separate department query
for each record, then load the ValueList of that query into a structure for
display in the appropriate column. This is very slow, since each time a
record is added, it means another query for this page.
I'd like to add this column as a subquery of the main query, but I don't
know how I can concatenate the results of the subquery to get multiple
values into one column.
Any suggestions?
Thanks - Eric
Here's the basic logic:
<cfquery name="main_query">
SELECT ID, Number, Title, Status, etc.
FROM a couple of tables
WHERE etc.
</cfquery>
<cfset stDepts= StructNew()>
<cfloop query="main_query">
<cfquery name="dept_query">
SELECT Department
FROM a couple tables
WHERE ID = #main_query.ID#
etc.
</cfquery>
<cfset stDepts[main_query.ID] = ValueList(dept_query.Department)>
</cfloop>
Questions about Salmon Recovery? Try the Salmon Information Center
www.salmoninfo.org or call 1-877-SALMON-9
- - - - - - - - - - - - -
Eric Maia, King County Water & Land Resources
(206) 296-8024 / [EMAIL PROTECTED]
201 S Jackson St., Room 703
Seattle, WA 98104
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
