Groupwise maximum record lookup for contracts and latest status

Question

I'm looking for help as I can't seem to get a handle on a SQL query.

I have two tables:

• Contracts - where the list of contracts is kept along with the associated partners

CREATE TABLE `contracts` (
  `id` varchar(5) NOT NULL,
  `partner` varchar(12) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

• locks - list of contract statuses

CREATE TABLE `locks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('unlock','templock','lock','permalock') DEFAULT NULL,
  `contractID` varchar(5) NOT NULL,
  `partnerID` varchar(12) NOT NULL,
  `stamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `user` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

I am trying to get a list of the latest contract statuses for a given partner:

SELECT c.id, c.partner ,l.`type`, l.stamp, l.id 
FROM contracts c 
LEFT JOIN locks l
ON c.id = l.contractID 
WHERE partner="2000000301"
GROUP BY c.id ASC

I was able to get the result, but it doesn't indicate the last status correctly - it "takes" the first one occurring :(, and I need the last one.

I tried the sequential approach, i.e. I first made a query that retrieves the last state for the contract:

SELECT max(l.id), l.partnerID ,l.contractID ,l.stamp ,l.`type`  
FROM locks l
WHERE l.partnerID = "2000000301" and l.contractID ="35274";

It works fine, but if I tried to implement this to the main query, using 'LEFT JOIN(...)'

SELECT *
FROM contracts
LEFT JOIN (
  SELECT max(ll.id), ll.contractID, ll.partnerID, ll.stamp, ll.`type`
  FROM locks ll
  WHERE ll.partnerID = contracts.partner <-- error :(
) l
ON contracts.id = l.contractID 
WHERE partner="2000000301"
ORDER BY contracts.id ASC

Here there is a problem with the availability of the 'contractID' field from the 'contracts' table and I get an error :(

SQL Error [1054] [42S22]: (conn=180) Unknown column 'contracts.partner' in 'where clause'

I had already lost the concept of approach.

Answer 1

I think it worked :)

SELECT c.id ,c.partner ,l.stamp ,l.`type`
FROM contracts c
LEFT JOIN (
    SELECT l.contractID, l.partnerID, l.stamp, l.`type`
    FROM locks l INNER JOIN (
      SELECT contractID, max(id) as max_id
      FROM locks
      group by contractID
    ) m
    ON l.contractID=m.contractID and l.id=m.max_id  
) l
ON c.id = l.contractID 
WHERE c.partner="2000000301"
ORDER BY c.id ASC