Some help needed with a SQL query

Question

Hello,

I need some help with a MySQL query. I have two tables, one with offers and one with statuses. An offer can has one or more statuses. What I would like to do is get all the offers and their latest status. For each status there's a table field named 'added' which can be used for sorting.

I know this can be easily done with two queries, but I need to make it with only one because I also have to apply some filters later in the project.

Here's my setup:

CREATE TABLE `test`.`offers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`client` TEXT NOT NULL ,
`products` TEXT NOT NULL ,
`contact` TEXT NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `statuses` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`offer_id` int(11) NOT NULL,
`options` text NOT NULL,
`deadline` date NOT NULL,
`added` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Answer 1

Should work but not very optimal imho :

 SELECT *
 FROM offers
 INNER JOIN statuses ON (statuses.offer_id = offers.id
     AND statuses.id = 
          (SELECT allStatuses.id
          FROM statuses allStatuses 
          WHERE allStatuses.offer_id = offers.id
          ORDER BY allStatuses.added DESC LIMIT 1))

Answer 2

Try this:

SELECT 
o.*
FROM offers o
INNER JOIN statuses s ON o.id = s.offer_id
ORDER BY s.added
LIMIT 1