Joining 3 tables while using MAX

Question

Overview

I am attempting to join 3 tables auction, bids, and users. I need several key pieces of information from each table based off the bids table. In the end I want a summary overview of the auctions. To see the latest bid for each auction and the user that placed it.

I am used to doing simple SQL queries and I am not sure what I need to do in order to accomplish this. Not looking for a solution (although I would take it with an explanation) but learning experience.

Table Structures

auctions

id, title, location

bids

id, auction_id, user_id, bid_amount

users

id, name, address

Query So Far

So far this is what I have started with, any guidance on what to do from here is greatly appreciated.

SELECT 
    MAX(bids.id) AS 'Bid ID',
    bids.auction_id AS 'Auction ID',
    auctions.title AS 'Auction Title',
    MAX(bids.bid_amount) AS 'Bid Amount'
FROM
    boardman_bidding.bids
        INNER JOIN
    boardman_bidding.auctions ON bids.auction_id = auctions.id
        INNER JOIN
    boardman_bidding.users ON bids.user_id = users.id
WHERE
    bids.auction_id > 1065 AND
    auctions.city_id = '45621'
GROUP BY bids.auction_id
ORDER BY bids.auction_id DESC;

Expected Output

I am hoping for the following:

bid_id, auction.title, auction.location, users.user_id, users.name, users.location, bid_amount

Answer 1

Assumption: bid.id is always incrementing. You said you want the latest bid. Technically, "the latest" is defined by time, not by an ID - but I do not see any time column against your bid table, so I assume the ID numbers are issued incrementally (always increasing). In this case, the max(ID) will be the latest.

Following should work. In theory you could move the subqueries into a join but would need to calculate the latest bid ID in the join condition, which is harder to read. As it is, the last three subqueries all have the same WHERE clause, so the optimiser should know to look up the table only once anyway, and obtain the 3 values required. (Unless you have millions of auctions running, I don't think performance will be any issue).

select
    auctions.title as Auction_Title,
    auctions.location as Auction_Location,
    (select max(bids.id) from bids where bids.auction_id = auctions.id) as Latest_Bid_ID,
    (select amount from bids where id = Latest_Bid_ID) as Bid_Amount,
    (select users.id from users join bids on bids.user_id = users.id where bids.id = Latest_Bid_ID) as User_ID,
    (select users.name from users join bids on bids.user_id = users.id where bids.id = Latest_Bid_ID) as User_Name,
    (select users.location from users join bids on bids.user_id = users.id where bids.id = Latest_Bid_ID) as User_Location
from
    auctions
order by auctions.title
;

Answer 2

Bids should be two tables:

• BidHistory -- containing details about each bid
• CurrentBid -- Either some details (redundant) about the latest bid, or where all the details live until moved to the other table.

This adds complexity to the "make a bid" code. But it simplifies the code that needs to know what the current bid is. And it avoids the complex code to fetch the latest bid.

The Strawman:

There is a serious flaw in the query that you presented. MAX(bid.id) and MAX(bids.bid_amount) do not necessarily refer to the same row. (OK, perhaps they do in a bidding situation, but you should not assume it!) This is where you need "Groupwise Max" solutions. See the [greatest-n-per-group] tag. There are a lot of discussions here and on stackoverflow.com about how to find the row that contains the max (or min) value of a particular column. Some solutions involve a subquery. Some solutions are slower than others.

See also http://mysql.rjweb.org/doc.php/groupwise_max