Mysql – Joining 3 tables while using MAX

greatest-n-per-groupmariadbMySQL

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

Best Answer

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
;

Related Solutions

Mysql – Slow query joining on subqueries with max

The problem with the query as it is written comes from two aspects:

Your subqueries are generating derived tables that are not correlated to the outer query, so MySQL is rendering all possible records from each of those subqueries into three temporary tables, and then joining them to the tables in the outer query, and
The join from the temporary tables to the outer tables isn't using any indexes, since the derived temporary tables don't have indexes.

@Mark Bannister has proposed a good rewrite of your query which should be a significant improvement, but I believe there is more that could be done.

I think the best way to write this query may be to write it from the inside out.

 select t.srs_id, t.status, t.position, t.wfrl2, t.wfrl3, 
    t.home_dept_cd, t.updated_by_eid, t.date, t.date_unix,
    ts.name as status_name,
    tp.name as position_name,
    da.dept_title as dept_name, da.cluster as service_center,
    tm.meta_value1 as form_type,
    tm.meta_value2 as target_employee_name,
    tm.meta_value3 as target_eid,
    trm.eid1 as initiator_eid,
    trm.eid2 as approver_eid,
    u1.name as target_name,
    u2.name as initiator_name,
    u3.name as approver_name,
    tt.value as transaction_type
from (
    SELECT tx.srs_id, tx.status, tx.position, tx.wfrl2, tx.wfrl3, 
    tx.home_dept_cd, tx.updated_by_eid, tx.date, tx.date_unix,
    (SELECT id FROM exp_z_srs_ticket_meta ezstm
     WHERE ezstm.srs_id = tx.srs_id ORDER BY version_id DESC LIMIT 1) as meta_latest_id
    FROM exp_z_srs_tickets tx

    where 1  

     and t.home_dept_cd in (007940,008431,...)

) t JOIN ...
LEFT JOIN ...

So now, the 't' table is not the tickets, but is a derived table itself with the records from the ticket table.

Instead of finding the id corresponding to the highest version for every possible srs_id in the exp_z_srs_ticket_meta table, we're doing individual lookups for only the rows in exp_z_srs_tickets we're interested in, based on the WHERE clause.

This implicit temporary table should be derived first by the optimizer, and the rest of your query would use joins or left joins as appropriate, to look up the values you need, using the column meta_latest_id. It seems like you could eliminate all of the other subqueries and replace them with joins, to join the single derived table to all the lookup tables you need. The derived table won't have indexes, but they are less important there, because they are more valuable to us for lookups, and the indexes on the lookup tables (if defined) should get used for the joins.

Mysql – Joining Three Tables by using IDs as Foreign Keys

You are on the right track. Don't let perceived complexity derail the design, as it is basically the standard way to represent many-to-many relationships. These tables are also a good place to store metadata or attributes of the entity relationship.

Your reasoning is mostly correct: there are data integrity benefits to using a DBMS-generated key in the intermediate table approach, but an integer isn't necessarily faster on key comparisons because ideally the joins would use indexes, not the column values.