Bob Imperial Application Specialist Department of Medicine http://medicine.med.unc.edu
-----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:4486 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
