that worked, thank you very much. So far out of all the resources I have used in the past, this one is by far the best.
Original Message: >From: "Pascal Peters" <[EMAIL PROTECTED]> >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: SQL Query Help (I'm stuck again) >Date: Wed, 10 Sep 2003 16:51:14 +0200 >You need ai.Reserve_Price in the select (NOT the aggregate function) AND the HAVING >clause. Because of the inner join, you will NOT get the records that did not receive >a bid. > > -----Oorspronkelijk bericht----- > Van: grovem [mailto:[EMAIL PROTECTED] > Verzonden: wo 10/09/2003 16:43 > Aan: CF-Talk > CC: > Onderwerp: RE: SQL Query Help (I'm stuck again) > > > > If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING > line, I do not get any errors, but I also return all records that have had a bid. > > When I ad the HAVING line [below] I receve the error > ---------------------------------------- > ODBC Error Code = 37000 (Syntax error or access violation) > [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that > does not include the specified expression 'MAX(ab.Bid_Amount)>ai.Reserve_Price' as > part of an aggregate function. > ---------------------------------------- > > <cfquery name="Auction_GetAuctions" datasource="#DatasourceName#" > dbtype="ODBC"> > SELECT ai.Product_ID, ai.Status, > MAX(ab.Bid_Amount) AS HighBid > FROM Auction_Items ai, Auction_Bids ab > WHERE ai.Status = 'ACTIVE' > AND ai.date_EndDate < #createODBCDate(now())# > AND ai.Product_ID = ab.Product_ID > GROUP BY ai.Product_ID, ai.Status > HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) > </cfquery> > > > basically this needs to pull all of the records that did not receive a bid > meeting or exceeding the reserve price. > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
