multi-table query with left and inner joins

[chiprivers]

In an earlier post I established I needed to use an INNER JOIN to ensure that I only pulled the most recent record from a table where there several duplicates but with different dates.  I had that working but now I have tried to add the rest of the compelxities to the query, I can't get it right!

 

I have the following tables:

 

CREATE TABLE `status` (
`statusID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`contractREF` int(10) unsigned NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`status_codeREF` int(10) unsigned DEFAULT NULL,
`resourcesREF` int(10) unsigned DEFAULT NULL,
`record_created` datetime NOT NULL,
PRIMARY KEY (`statusID`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1


CREATE TABLE `status_codes` (
`status_codeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` varchar(50) NOT NULL,
`indicator` varchar(10) NOT NULL,
`available` int(1) unsigned NOT NULL,
`record_status` int(1) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`status_codeID`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1


CREATE TABLE `resources` (
`resourcesID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`resource_typesREF` int(10) unsigned NOT NULL,
`indicator` varchar(5) NOT NULL,
`stationsREF` int(10) unsigned NOT NULL,
`record_status` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`resourcesID`)
) ENGINE=MyISAM AUTO_INCREMENT=131 DEFAULT CHARSET=latin1 

 

The 'status_codeREF' and 'resourcesREF' columns in the 'status' table reference the 'status_codeID' column in the 'status_code' table and the 'resourcesID'  column in the 'resources' table respectively.

 

I need to extract 'time' from the 'status' table, 'status', 'indicator' and 'available' from the 'status_code' table and 'indicator' from the 'resources' table.

 

The results should be limited to those where the 'contractREF' column in 'status' is equal ? and 'date' in 'status' is equal to ?.

 

There may be multiple duplicate records which share the same 'contractREF' and 'date' values and these should be limited to returning only the record with the most recent 'record_created' value.

 

The query that I have put together is:

 

SELECT
s.time, c.status, c.indicator AS s_indicator, c.available, r.indicator AS r_indicator
FROM
status AS s
INNER JOIN (
SELECT 
MAX(record_created) AS current_record 
FROM 
status  
WHERE 
contractREF = 1
AND
date = '2010-09-10'
GROUP BY 
time, contractREF
) AS x
ON 
s.time = x.current_record
LEFT JOIN
status_codes AS c
ON
s.status_codeREF = c.status_codeID
LEFT JOIN
resources AS r
ON
s.resourcesREF = r.resourcesID
ORDER BY
s.time ASC

 

This query validates OK but does not return any records when I know there should be some if I had the statement correct.

[chiprivers]

Perserverence is the key!

 

I have managed to come up with the following query which appears to work:

SELECT
s.time, c.status, c.indicator AS s_indicator, c.available, r.indicator AS r_indicator
FROM
status AS s
INNER JOIN
(SELECT MAX(record_created) AS this_created FROM status WHERE contractREF = 1 AND date = '2010-09-10' GROUP BY time, contractREF) AS j
ON
s.record_created = j.this_created
LEFT JOIN
status_codes AS c
ON
s.status_codeREF = c.status_codeID
LEFT JOIN
resources AS r
ON
s.resourcesREF = r.resourcesID
ORDER BY
s.time

 

All I had actually got wrong was one column name in the inner join on condition!  :-\