table then do a self join to with bid_amount, product_id and return
member_ID then join to the other tables. You will probably want to do a
join to the auction_bids as well to try to restrict the number of rows you
will use.
Assuming you are using MS SQL it might look something like this?
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 (SELECT MAX(abt.Bid_Amount),
abt.product_id,
abw.Member_ID
FROM Auction_Bids abt, Auction_Items ait, Auction_Bids abw
WHERE ai.status = 'active'
AND ait.date_enddate < GETDATE()
AND abt.bid_amount >= ait.reserve_price
AND ait.product_ID = abt.Product_ID
AND abt.Bid_Amount = abw.Bid_Amount
AND abt.product_id = abw.product_id) ab, Auction_Items ai,
member_list ml
WHERE ab.Member_ID = ml.member_ID
ORDER BY ai.date_enddate, ai.product_ID, ab.bid_amount DESC
This assumes you don't have a "tie" for a winning bid.
Kore Peterson
Michael Grove
<[EMAIL PROTECTED] To: CF-Talk <[EMAIL PROTECTED]>
.com> cc:
Subject: HELP!!! I have a SQL Call That I cannot
01/22/2004 Make work...
10:12 AM
Please respond
to cf-talk
I have been working on this for days and can not figure it out. I think it
would be somthing easy, but just cant get it.
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]
