Return only one item related to the main table

Question

I have two tables, respostas and respostas_log, where I store information of the answers in answers and some others that have to go logging in reply_log. I am needing in my SELECT to bring only the last log of each answer.

Example of tables and contents:

CREATE TABLE IF NOT EXISTS `respostas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `responsavel_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `respostas_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `respostas_id` int(11) NOT NULL,
  `opt_aplicavel` tinyint(1) DEFAULT NULL,
  `txt_resposta` text,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `respostas_id` (`respostas_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `respostas` (`id`, `responsavel_id`) VALUES
(1, 1),
(2, 3);

INSERT INTO `respostas_log` (`id`, `respostas_id`, `opt_aplicavel`, `txt_resposta`, `created_at`) VALUES
(1, 1, 1, 'Resposta 1 de 1', '2014-05-13 00:00:00'),
(2, 1, 1, 'Resposta 2 de 1', '2014-05-13 00:00:00'),
(3, 1, 0, 'Resposta 3 de 1', '2014-05-13 00:00:00');

And the query I tried

SELECT  r.id,
    r.responsavel_id,
    l.opt_aplicavel, 
    l.txt_resposta,
    max(l.id)

FROM respostas r

LEFT JOIN respostas_log l ON l.respostas_id = r.id

GROUP BY  r.id,
    r.responsavel_id,
    l.opt_aplicavel, 
    l.txt_resposta

Example in Sqlfiddle

Of this example, it would be to bring only the first and last line

Answer 1

You can get the last log id with a subquery:

SELECT  r.id,
        r.responsavel_id,
        l.opt_aplicavel,
        l.txt_resposta,
        l.id idLog
FROM respostas r

LEFT JOIN respostas_log l
  ON l.id = (select max(id) from respostas_log l2 where l2.respostas_id = r.id)

I don’t think it’s possible, in a single select, get the id the last log and the details of this log.

To find out the value of the last log, you will need an aggregator function (which is the case with MAX). By aggregating by id, the other returned fields (opt_aplicavel and txt_resposta) need to be grouped by each existing value. Grouping by these fields would cause MAX return different values for each record, the aggregated value needs to be obtained in a separate query, and then used in the query that will return the other fields.

This Mysql 5.0 documentation article demonstrates a way to make subquery not "correlate" to the main query, which can improve performance:

SELECT  r.id,
        r.responsavel_id,
        l.opt_aplicavel, 
        l.txt_resposta,
        l.id idLog
FROM respostas r

LEFT JOIN (select respostas_id, max(id) id from respostas_log group by respostas_id) l2
  ON l2.respostas_id = r.id

LEFT JOIN respostas_log l
  ON l.id = l2.id;

Both examples in SQL Fiddle

If the performance of both cases is below the expected, it may be the case to create an index that facilitates the query.