I am working on another SQL query for an auction project in CF and I am very stuck. I think the answer should be very simple, but I have tried manipulating the query every way I can and cannot receive the correct data. For this query I am working with two tables using a Microsoft Access 2000 database.. Table 1. Auction_Items (Table Name) Product_ID (auto number/ Key) Date_EndDate (date/time) Reserve_Price (Currency) Status (Text) [ options are Active and Closed ] Table 2. Auction_Bids (Table Name) Product_ID (auto number/ Key) Member_ID (number) [ this is the person that placed the bid.] Bid (Currency) In a nutshell I am trying to build two queries (the first, which someone here already helped me resolve) The purpose of the first query is to select all of the products that that have a status of active, an EndDate less than today and a bid that has exceeded the reserve price. The query (SAMPLE 1) seems to work fine for this. SAMPLE 1 SELECT ai.Product_ID, ai.Status, ai.reserve_price, ai.date_enddate, MAX(ab.Bid_Amount) AS HighBid, Max(Bid_Amount*00.015) As Charge FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate < #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID AND ai.Reserve_Price <= ab.Bid_Amount GROUP BY ai.Product_ID, ai.Status, ai.reserve_price, ai.date_enddate THIS IS THE ONE I CANT GET TO WORK The second query that I am having trouble (SAMPLE 2) with should do the exact same thing except it should take the records that do not have bids meeting and/or exceeding the reserve price and automatically setting their status to closed. I would think that you would simply take the ai.Reserve_Price <= ab.bid_amount and change it to >, but that returns too many records. It returns all the records that have bids less then the reserve rather than returning records that only have bids less then the reserve. SAMPLE 2 UPDATE Auction_Items SET Status = 'CLOSED' WHERE EXISTS (SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate < #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID AND ai.Reserve_Price > ab.Bid_Amount GROUP BY ai.Product_ID, ai.Status); SAMPLE DATA Auction_Items Product_ID Date_EndDate Reserve_Price Status 1 9/8/03 $1000 ACTIVE 2 9/8/03 $500 ACTIVE 3 9/8/03 $200 ACTIVE Auction_Bids Product_ID Bid_Amount 1 $200 1 $1100 2 $400 2 $450 3 $200 Using the sample data, Query two should basically automatically set Product_ID 2's status to CLOSED since there is no bid that meets or exceeds the reserve price. With my current query, it closes them all because they each have had a bid under the reserve price. I hope that makes sense.Thanks :-)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
