Return only one item related to the main table

Question

Return only one item related to the main table

Navigation

#1 by (2 votes)

1

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 replies_log. I need in my SELECT to bring only the last log of each response.

Example tables and content:

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

SQLFiddle Example

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

mysql modelo-relacional

asked by anonymous 13.05.2014 / 22:42

1 answer

Load and read XML via AJAX Cross-Domain how to pass an array with N arguments to a bind_param inside a class?

score 2 · Accepted Answer

You can get the id of the last log 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 do not think it's possible, in a single select , to get 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 ). When aggregating by id , the other returned fields ( opt_aplicavel and txt_resposta ) need to be grouped by each existing value. How to group by these fields would cause MAX to return different values for each record, the aggregate value needs to be obtained in a separate query, and then used in the query that will return the other fields.

This article from the MySQL 5.0 documentation demonstrates a way to make the subquery not "correlated" 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 lower than expected, it may be the case to create an index to facilitate the query.