Getting the best results at JOIN

Question

I have three tables; Auctions, Auction Offers and Users. The table structure looks something like this:

Auctions: id title -- ----- 1 Auction 1 2 Auction 2 Auction Bids: id user_id auction_id bid_amt -- ------- ---------- ------- 1 1 1 200.00 2 2 1 202.00 3 1 2 100.00 

Users is a standard table with an identifier and username.

My goal is to join these tables so that I can get the highest values ​​for these bids and also get the usernames associated with these bids; so I have the result like this:

 auction_id auction_title auctionbid_amt user_username ---------- ------------- -------------- ------------- 1 Auction 1 202.00 Bidder2 2 Auction 2 100.00 Bidder1 

So far, my request is as follows:

 SELECT a.id, a.title, ab.bid_amt, u.display_name FROM auction a LEFT JOIN auctionbid ab ON a.id = ab.auction_id LEFT JOIN users u ON u.id = ab.user_id GROUP BY a.id 

This gets the single lines I follow, but seems to display the lowest bid_amt, not the highest.

Answer 1

You can use the MAX function and subselection to get the maximum bid for each auction. If you join this subquery with your other tables and specify the where clause as follows, you should get what you are looking for.

 SELECT a.id, a.title, ab.bid_points, u.display_name FROM Auction AS a INNER JOIN (SELECT auction_id, MAX(bid_points) AS maxAmount FROM auction_bids GROUP BY auction_id) AS maxBids ON maxBids.auction_id = a.id INNER JOIN auction_bids AS ab ON a.id = ab.auction_id INNER JOIN users AS u ON u.id = ab.user_id WHERE ab.auction_id = maxBids.auction_id AND ab.bid_amount = maxBids.maxAmount 

Hope this helps.

Answer 2

This is a typical intra-group cumulative problem. You can solve this using the so-called left self-destruction union

Try the following:

 SELECT a.id, a.title, ab.bid_points, u.displayname FROM auction a INNER JOIN auction_bids ab ON ab.auction_id = a.id LEFT JOIN auction_bids b1 ON ab.auction_id = b1.auction_id AND ab.bid_points < b1.bid_points LEFT JOIN users u ON u.id = ab.user_id WHERE b1.auction_id IS NULL 

It basically creates a connection between the left and right sides until it no longer finds one for the left side, and then this is the highest element.

Another solution would be to use multiple queries (of course) or a temporary pivot table.

Answer 3

Try the following:

 SELECT a.id, a.title, ab.bid_points, u.display_name FROM auction a LEFT JOIN auctionbid ab ON a.id = ab.auction_id LEFT JOIN users u ON u.id = ab.user_id GROUP BY a.id ORDER BY ab.bid_points DESC 

If this does not work, try using a subtitle in auctions containing something like

 SELECT id, user_id, auction_id, MAX(bid_amt) FROM action_bids GROUP BY auction_id 

Answer 4

Try adding the following sentence; unsure of performance.

 WHERE NOT EXISTS (SELECT * FROM auctionbid abhigher WHERE abhigher.auction_id = ab.auction_id AND abhigher.auctionbid_amt > ab.auctionbid_amt) 

Excludes auction bids from a higher bid request for the same auction.

The only problem is that if you have 2 identical bets and they will be listed. One way to get rid of them - but this is a relatively arbitrary choice of the winner, is to use the bid identifier:

 WHERE NOT EXISTS (SELECT * FROM auctionbid abhigher WHERE abhigher.auction_id = ab.auction_id AND abhigher.auctionbid_amt >= ab.auctionbid_amt AND abhigher.id > ab.id) 

Answer 5

Here is what you can try ... like in the old school .. nothing new ... no need to go to the left connection or something else ... rest depends on your exact requirement

 select A.id,A.title,max(AB.bid_amt),name from Auction A,AuctionBids AB,Users U where U.ID=AB.USER_ID AND A.ID=AB.ID group by A.ID,A.title,name