...use... AND lookup.type_id IN (Select type_id from lookup where lookup.type = '#form.steeltype#') ...instead of... AND lookup.type_id = (Select type_id from lookup where lookup.type = '#form.steeltype#')
-Michael Conger [EMAIL PROTECTED] ----- Original Message ----- From: "Webmaster" <[EMAIL PROTECTED]> Newsgroups: cf-talk Sent: Friday, October 04, 2002 2:35 PM Subject: loop problems > This is the query I am running but because I have multiple entries going in > to this form that gets submitted. The bottom part of query does not work > because it is a list how do I get it to look at each individual record and > loop through for each one? > > <CFQUERY NAME="fullcheckquery" DATASOURCE="brinventory"> > SELECT Steel.width, > Steel.weight, > Steel.length, > Steel.quantity, > Steel.price, > location.location, > lookup.type, > lookup2.type as smalltype, > method.method, > size_lookup.size_type > FROM Steel, > location, > lookup, > lookup2, > method, > size_lookup > WHERE steel.type_id = lookup.type_id > AND steel.typeid = lookup2.typeid > AND steel.size_id = size_lookup.size_id > AND steel.location_id = location.location_id > AND steel.method_id = method.method_id > AND lookup.type_id = (Select type_id from lookup where lookup.type = > '#form.steeltype#') > </CFQUERY> > <CFLOOP QUERY="fullcheckquery"> > <CFIF (form.steeltype EQ fullcheckquery.type) AND (form.type EQ > fullcheckquery.smalltype) AND (form.width EQ fullcheckquery.width) AND > (form.weight EQ fullcheckquery.weight) AND (form.length EQ > fullcheckquery.length) AND (form.price EQ fullcheckquery.price) AND > (form.location EQ fullcheckquery.location)> > <CFQUERY DATASOURCE="brinventory" NAME="update"> > SELECT steel.id, > steel.quantity > FROM Steel, > lookup, > lookup2, > location > WHERE steel.type_id = lookup.type_id > AND steel.typeid = lookup2.typeid > AND steel.location_id = location.location_id > AND lookup.type_id = (Select type_id from lookup where lookup.type = > '#form.steeltype#') > AND lookup2.typeid = (Select typeid from lookup2 where lookup2.type = > '#form.type#') > AND Steel.width = '#form.width#' > AND Steel.weight = '#form.weight#' > AND Steel.length = '#form.length#' > AND steel.price = '#form.price#' > AND location.location_id = (Select location_id from location where > location.location = '#form.location#') > </CFQUERY> > > <CFSET setquantity =update.quantity + form.quantity> > > <CFQUERY DATASOURCE="brinventory" NAME="update"> > UPDATE Steel > SET quantity = #setquantity# > WHERE steel.id = #update.id# > </CFQUERY> > <CFELSE> > <CFOUTPUT>This is a new entrie!</CFOUTPUT> > </CFIF> > </CFLOOP> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
