Select last JOIN record

Question

Select last JOIN record

Navigation

#1 by (3 votes)

4

I have a table tb_processos and another tb_detalhes (it keeps details of a certain process), I would like to get all the process data and only the last detail of the process.

The way I'm doing I search for all records of tb_detalhes related to a given process:

SELECT 
    tb_processo.nm_empresa,
    tb_processo.dt_solicitacao,
    tb_detalhes.desc_detalhes
FROM
    tb_processo 
    LEFT JOIN tb_detalhes ON tb_detalhes.id_processo = tb_processo.id_processo

However, I would like to get only the last record of tb_detalhes , ie I will display all the data in the process and the desc_detalhes of the last record entered, how can I do this?

mysql sql mysqli

asked by anonymous 16.03.2016 / 13:21

1 answer

Automatic class generator Working with Canvas - Borderless

score 3 · Accepted Answer

What you need is SubQuery not LEFT JOIN .

SELECT 
    tb_processo.nm_empresa,
    tb_processo.dt_solicitacao,
    (
        SELECT  tb_detalhes.desc_detalhes
        FROM    tb_detalhes
        WHERE   tb_detalhes.id_processo = tb_processo.id_processo 
        ORDER BY tb_detalhes.id_detalhes DESC
        LIMIT 1
    ) AS ultimo_detalhe
FROM
    tb_processo

In this way you are searching within query tb_processo , a single record of the details table that belongs to the current process and be the last one ( ORDER BY id_detalhes DESC )