you could run a QoQ inside a loop of queryA, or, since it looks like both queries have at least one column in common, use one original query to pull all the data, then do a cfoutput with GROUP attribute.
it's hard to suggest anything more specific since your code is so very non-specific. and do you really need to use evaluate() function there? Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ On 03/08/2009 23:58, patrick buch wrote: > > Hi, > > I'm stumped on how to match data from 2 queries. What is the best way? > Here's the situation.... > > Query A > -------------- > <CFQUERY NAME="qSSR" DATASOURCE="#DSN#"> > SELECT #ssr_tablename#.mat_grp, #ssr_tablename#.plant, sloc, mat_id, > mat_desc, valuated_stock_unrestricted, rounding_value, rop, format, > SUM(PO_Qty) as po_qty > FROM #ssr_tablename# > INNER JOIN #tablename# > ON (#ssr_tablename#.plant = #tablename#.plant) > WHERE (#ssr_tablename#.mat_id = #tablename#.matnum) > AND #ssr_tablename#.plant IN ('SI**', 'SI**') > GROUP BY mat_id > order by #ssr_tablename#.plant, mat_id > </CFQUERY> > > QueryB > --------------------------- > <CFQUERY NAME="qUsage7" DATASOURCE="#DSN#"> > SELECT count(distinct mdoc) AS Usage7, matnum, postdate FROM table1 > INNER JOIN #ssr_tablename# > ON (table1.matnum = #ssr_tablename#.mat_id) > where plant IN ('SI**', 'SI**') > and mvt = '201' > and DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= postdate > GROUP BY matnum > order by plant; > </CFQUERY> > > Output For QueryA > ************************* > <cfloop query="qSSR"> > <tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#"> > <cfloop index="f" list="#ssr_fields#"> > <cfset bg="ffffff"> > <td align="right"><cfif evaluate("#f#") eq "" OR > left(trim(evaluate("#f#")),"1") eq > "0"> </cfif>#trim(evaluate("#f#"))#</td> > </cfloop> > </tr> > </cfloop> > > Basically, during the loop of queryA I want it to, if it finds a match, to > output from QueryB or else just leave blank and move on to the next row... > > Should this be done before hand in a query so all I have to do is output the > query data or can this be done on the backend via the CF page? > > Thanks as always for your time and response(s)... > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:325181 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
