Duh ... sorry about that last send :) Just updated one of the other records with a value of 'abstain' and it looks like I'll have to work/learn a little more about using case. Thanks for the direction folks! I get tossed stuff here that nobody else really wants so I am more like Mikey(he'll eat anything) for those of you that know that reference :). It's still all new to me but I love it, especially when it all works.
Bob -----Original Message----- From: Greg Morphis [mailto:[email protected]] Sent: Monday, March 30, 2009 2:28 PM To: cf-newbie Subject: Re: query help Try some simple debugging first... <cfquery name="getpackets" datasource="#dsdata#"> select Id, name from review_packets order by Id </cfquery> <cfoutput query ="getpackets"> <cfdump var="#getpackets#" /> <cfquery name="getvotes" datasource="#dsdata#"> select vote, count(vote) as thisvote from packet_votes where packet_id = #getpackets.id# group by vote </cfquery> <cfdump var="#getnotes#" /> 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 this is not tested but I thi ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:4487 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
