dmcoding dmcoding - 5 months ago 9
MySQL Question

get data based on MAX date and customer id

I have two tables: customers and contracts. The common key between them is customer_id. I need to link these two tables to represent if my fictitious business is on contract with a customer.

The customer -> contract table has a one to many relationship, so a customer can have an old contract on record. I want the latest. This is currently handled by contract_id which is auto-incremented.

My query is supposed to grab the contract data based on customer_id and the max contract_id for that customer_id.

My query currently looks like this:

SELECT * FROM(
SELECT co.*
FROM contracts co
LEFT JOIN customers c ON co.customer_id = c.customer_id
WHERE co.customer_id ='135') a
where a.contract_id = MAX(a.contract_id);


The answer is probably ridiculously obvious and I'm just not seeing it.

Answer

Since the most recent contract will be the one with the highest a.contract_id, simply ORDER BY and LIMIT 1

SELECT * FROM(
    SELECT co.* 
        FROM contracts co
        LEFT JOIN customers c ON co.customer_id = c.customer_id 
        WHERE co.customer_id ='135') a 
ORDER BY a.contract_id DESC
LIMIT 1