Well I was able to get this far and see that the dump does output the
correct counts doing this, now to figure out how to get the counts for
each group(yes,no,abstain) into a readable formatted output. :) Seems
like this should be easier than it has been for me but then, I work
alone and am left to my own devices however feeble they may be :-)
Trudging ahead at any rate,
Thanks again.
<cfquery name="getpackets" datasource="#dsdata#">
select Id, name
from review_packets order by Id
</cfquery>
<cfloop query="getpackets">
<cfquery name="countedVotes" datasource="#dsdata#">
select packet_id, vote, count(vote) as thisvote
from packet_votes where packet_id = #getpackets.id#
group by vote
</cfquery>
<cfdump var="#countedVotes#" />
</cfloop>
Also try looping over the recordset
<cfquery name="foo" datasource="ds_foo">
select id, name
from my_table
</cfquery>
<cfloop query="foo">
<cfquery name="goo" datasource="ds_foo">
select value_1, value_2
from my_second_table
where id = #foo.id#
</cfquery>
<cfdump var="#goo#">
</cfloop>
You could also try doing this in one query...
select Id, name, vote, count(vote) as thisvote
from review_packets rp
left join packet_notes pn on on.packet_id = rp.id
group by id, name, vote
order by name
first gotta find out if the problem is in your data or query..
if MySQL supports case satements then you could do it in the query..
select id, name,
(case vote when 'yes' then 1 else 0 end) as yes_votes,
(case vote when 'no' then 1 else 0 end) as no_votes,
(case vote when 'abstain' then 1 else 0) as abstain_votes
from my_table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4489
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
