I have an auction site that I am working on. I have two tables, Auction_Item table and Auction_Bid table.
The item table houses the product id, auction close date, the reserve price and the seller id. The bid table houses the product_id, bid amount and bidder id.
What I want to do is show a list of auctions that have reached their close date and have a bid exceeding the reserve price.
I can do this. The trickey part is, I dont want to see all of the bids exceeding the reserve. Only the highest bid.
here is what I have so far....
<cfquery name="Auction_Sale" datasource="#DatasourceName#" dbtype="ODBC">
SELECT ab.Bid_Amount as HighBid, ab.Member_ID as Bidder, ab.product_id,
ai.reserve_price, ai.date_enddate as ENDDATE, ai.product_identifier, ai.member_ID as SELLER,
ml.userid as BIDDER_NAME
FROM Auction_Bids ab, Auction_Items ai, member_list ml
WHERE ai.status = 'active'
AND ai.date_enddate < #createODBCdate(now())#
AND ab.bid_amount >= ai.reserve_price
AND ai.product_ID = ab.Product_ID
AND ab.Member_ID = ml.member_ID
ORDER BY ai.date_enddate, ai.product_ID, ab.bid_amount DESC</cfquery>
What am I missing?
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
