Okay all, I found the problem and you're not gonna believe this. This is the debugging from my queries (I don't have a location of "blah", so the records should be zero)...
Attempt 1: Nominations1 (Records=5, Time=0ms) SQL = Select * From Nominations Where Company_No=4 Nominations2 (Records=5, Time=0ms) SQL = Select * From Nominations Where Company_No=4 And Location_Code='blah' Nominations3 (Records=0, Time=0ms) SQL = Select * From Nominations Where Location_code='blah' Attempt 2: Nominations1 (Records=5, Time=0ms) SQL = Select * From Nominations Where Company_No=4 Nominations2 (Records=0, Time=0ms) SQL = Select * From Nominations Where Company_No=4 AND Location_Code='blah' Nominations3 (Records=0, Time=0ms) SQL = Select * From Nominations Where Location_code='blah' Notice that Nominations2 in attempt1 uses "And", resulting in 5 records. Now notice that Nominations2 in attempt2 uses "AND", resulting in 0 records. Is this a bug? Does a query of query ignore the "and" clause if it's not capitalized? Ryan -----Original Message----- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 10:13 AM To: CF-Talk Subject: RE: query of query problem (again) Ryan, Some problems (or nuances) are: The column names are case sensitive - make them all upper case. The "SUM( )" feature does not work in tandem with the WHERE clause - it returns the sum of ALL, and does not apply the where filter. The same may be true of AVG() - but I'm not sure If you are joining 2 tables together you must qualify the query.columname in each select (select query1.column1 AS columnA, query2.column1 AS columnB etc. That's my short list. Mark -----Original Message----- From: Ryan Pieszak [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 8:47 AM To: CF-Talk Subject: query of query problem (again) Hello all, I posted something similar yesterday, but I have an easier situation this time. Has anybody had any trouble with query of query in CF5? I'm not doing anything complicated, but my query of a query seems to pull too many records. Thanks for any insight. Ryan J. Pieszak Applications Engineer Ziphany, LLC. (716) 510-0379 www.ziphany.com ______________________________________________________________________ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
