SELECT max(ab.Bid_Amount) as HighBid, ai.product_id,
ai.reserve_price, ai.date_enddate as ENDDATE, ai.product_identifier,
ai.member_ID as SELLER
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
GROUP BY ai.product_id, ai.reserve_price, ai.date_enddate,
ai.product_identifier, ai.member_ID
ORDER BY ai.date_enddate, ai.product_ID
Note that I removed some of the ab.[User] stuff. I don't think you can
grab that at the same time as the rest of the query, except maybe using
some tricks with subqueries. But I would just pull this base data, and
then worry about finding the bidder's ID and name in a 2nd step.
If you have Stored Procedures available, then you can do it all there.
What is your database system?
-----Original Message-----
From: Michael Grove [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 10:08
To: SQL
Subject: HELP!!! I have a SQL Call That I cannot Make work...
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]
