0

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

  • 1
    Showing expected output would help. I suspect something like this will get you on the right path. MySQL-8/MariaDB-10.2 windowing functions help with this. – danblack Nov 21 '18 at 23:46
0

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
;
| improve this answer | |
  • This worked, thanks so much! My mistake was not using the joins in the subquerie. Thanks again! – Captain Harkness Nov 23 '18 at 15:34
  • The other thing to note - even though you said you were interested in "the latest bids", it isn't the bids table in the main (outer) query - it's the auctions table. Why? Because you want the latest bids for each auction. So really you want to list the auctions, and for each auction, calculate the latest bid. I note this only because the original question began with the bids table and then joined the others. This understanding of the problem helps understand which table you really want to query first. Cheers. – youcantryreachingme Nov 25 '18 at 22:06
0

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

| improve this answer | |
  • Unfortunately I am working with something I did not build and it is already in production. However, that is good information to think about as I am building a system from the ground up to replace what we have currently for next years auction. – Captain Harkness Nov 22 '18 at 0:12
  • @CaptainHarkness - I added to my Answer. – Rick James Nov 22 '18 at 0:21
  • Thanks - I will take a look! The MAX just happens to work and I was only doing it to get the max of both. – Captain Harkness Nov 22 '18 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.