The only dumb questions are the ones not asked! hehehe, I don't know the answer though sorry. :-(
Mike ----- Original Message ----- From: "Julia Green" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, October 04, 2001 10:29 AM Subject: Important - For a project -- please help > 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 > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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
