CODEWITHSUNDEEP
mysql
dmcoding
dmcoding

Reputation: 331

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.

Upvotes: 0

Views: 93

Answers (2)

sagi
sagi

Reputation: 40491

You can use NOT EXISTS() :

SELECT * FROM contracts c
LEFT JOIN customers co
 ON(co.customer_id = c.customer_id)
WHERE co.customer_id = '135'
 AND NOT EXISTS(SELECT 1 FROM contracts co2
                WHERE co2.customer_id = co.customer_id
                  AND co2.contract_id > co.contract_id)

This will make sure it's the latest contract, it is dynamic for all customers, you can just remove WHERE co.customer_id = '135' and you will get all the results.

In general, you can't use an aggregation function on the WHERE clause, only on the HAVING() which will be usually combined with a GROUP BY clause.

Upvotes: 1

ʰᵈˑ
ʰᵈˑ

Reputation: 11365

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

Upvotes: 1

Related Questions