Can you use counts with joined tables and a where statement? This seems like a dumb question...
Goto: http://www.cheshiregroup.com/gilway/subcount3.cfm My client wants the count of each separate number...next to the number preferably... This is tricky because there is 2 tables, one that takes the input of the form with the customer's number(tblfile), and the other that has all the valid numbers (gilway12). I got just the valid numbers to come up on subcount3.cfm, by using aliases and a where statement and joining tables. Then he said he wanted a count and I can't get it to work with the code... Below is the sql code for the statement using File in both tables... I keep getting errors around the WHERE statement when it worked in subcount3.cfm... <CFQUERY NAME="gilwaya" DATASOURCE="cgi2"> SELECT count(t.File) as file3, g.File, t.File FROM tblfile t, gilway12 g GROUP BY g.File WHERE t.File=g.File </CFQUERY> produces the incorrect code. <CFQUERY NAME="GetQuery" DATASOURCE="cgi2"> SELECT count(File) AS file2 FROM tblfile </cfquery> <CFQUERY NAME="gilwaya" DATASOURCE="cgi2"> SELECT t.File, g.File FROM tblfile t, gilway12 g WHERE t.File=g.File ORDER BY t.File DESC </CFQUERY> produces subcount3.cfm From: "Mark Stewart" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, October 04, 2001 10:17 AM Subject: Stored Procedure Question > All, > > I've been using stored procedures for a while now but I've never really > needed to create one that returned multiple result sets. My question is, > how do you do that? Would someone be kind enough to show me a little > pseudo code or a snippet of code on how to accomplish this? > > I assume in cf, I would build my cfstoredproc like this: > > <cfstoredproc procedure="procedure_name" datasource="#maindsn#"> > <cfprocresult name="result1"> > <cfprocresult name="result2"> > </cfstoredproc> > > But I draw a blank when it comes to building the stored proc in SQL > Server where it would return multiple results. I assume the following: > > create procedure [procedure] as > > select ... from ... where... > > select ... from... where... > > How do I specify what query goes with what result? > > Thanks for your help! > > Mark > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
