Instead of this:
<cfloop query="zip">
<cfif story.fdd_id EQ zip.fdd_id>
(#NumberFormat(zip.fdcount)#)
</cfif>
</cfloop>
Use a QofQ (Query of Queries) - it will be faster:
<cfquery dbtype="query" name="qZipCheck">
SELECT fdcount FROM zip WHERE zip.fdd_id = <cfqueryparam
value="#story.fdd#" cfsqltype="cf_sql_integer">
</cfquery>
(#NumberFormat(qZipCheck.fdcount)#)
This will avoid having to loop through your zip query for every time through
your story loop. Also, I assumed your fdd_id is an integer, if it's not,
use cf_sql_varchar for the cfsqltype value instead.
Dave
-----Original Message-----
From: Barry Mcconaghey [mailto:bmcconag...@gmail.com]
Sent: Tuesday, February 16, 2010 7:53 AM
To: cf-talk
Subject: Re: Total Two Field From Two Tables
Good Morning.
This code works below but I'm not sure if there is a better way. Please let
me know.
<cfquery name="Zip" datasource="#request.dsn#">
SELECT a.zipcode, f.fdd_zip, f.fdd_name, f.fdd_state, f.fdd_id,
count(f.fdd_id) AS FDCount
FROM articles a, fddirectory f
WHERE a.zipcode = LEFT(f.fdd_zip, 5)
AND f.fdd_state = <cfqueryparam value="#URL.statecodeID#"
cfsqltype="CF_SQL_VARCHAR">
GROUP BY f.fdd_name
ORDER BY f.fdd_name ASC
</cfquery>
<cfquery name="Story" datasource="#request.dsn#">
SELECT F.fdd_id, F.fdd_name, F.fdd_zip, S.statename, S.abrev
FROM fddirectory F, states S
WHERE F.fdd_state = <cfqueryparam value="#statecodeID#"
cfsqltype="CF_SQL_VARCHAR">
AND F.fdd_state = S.abrev
ORDER BY fdd_name ASC
</cfquery>
<!---Display--->
<cfoutput query="story">
#story.fdd_name#
<cfloop query="zip">
<cfif story.fdd_id EQ zip.fdd_id>
(#NumberFormat(zip.fdcount)#)
</cfif>
</cfloop>
<br>
</cfoutput>
Thanks again for your time,
Barry
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330773
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
