Hello Everybody.
I have been thinking and working on this for three days now. I'm looking for
some help totaling two fields from two tables.
Here is what I have:
Sample Tables:
Table - fdd_directory
fdd_name (Example: ACME Fire Dept)
fdd_state (Example: PA)
fdd_zip (Example: 12345)
Table - states
statecodeID (Example: 111)
abrev (Example: PA)
statename (Example: Pennsylvania)
Table - articles
zipcode (Example: 12345)
<!---Here is what I have:--->
<cfquery name="states" datasource="#dsn#">
SELECT count(fddirectory.fdd_id) AS FDCount, states.statename,
states.abrev, states.statecodeID
FROM fddirectory, states
WHERE fddirectory.fdd_state = states.abrev
GROUP BY statename
ORDER BY statename
</cfquery>
<cfoutput query="states">
<a href="next.cfm?statecodeID=#abrev#">#states.statename#
(#NumberFormat(fdcount)#)</a><br />
</cfoutput>
So far, so good...
Next.cfm
<cfquery name="Story" datasource="#dsn#">
SELECT fddirectory.fdd_id, fddirectory.fdd_name, fddirectory.fdd_zip,
fddirectory.fdd_state, states.abrev
FROM fddirectory, states
WHERE fddirectory.fdd_state = <cfqueryparam value="#statecodeID#"
cfsqltype="CF_SQL_VARCHAR">
AND fddirectory.fdd_state = states.abrev
ORDER BY fddirectory.fdd_name
</cfquery>
<!---Here is where I would like to total articles.zipcode =
fddirectory.fdd_zip--->
<cfoutput query="Story">
<a href="next1.cfm?fdd_id=#story.fdd_id#">#story.fdd_name#</a> (Total Here)<br>
</cfoutput>
"Total Here" should equal articles.zipcode = fddirectory.fdd_zip or the number
of article(s) from the articles table that match each fdd_name.
For Example:
ACME FD (Total 38)
Thanks,
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:330740
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
