Select last JOIN record

Asked

Viewed 2,544 times

4

I have a table tb_processos and another tb_detalhes (saves details of a particular process), would like to take all the data of the process and only the last detail of the process.

The way I’m doing I search all the records of tb_detalhes related to a particular 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 take only the last record of tb_detalhes, that is to display all process data and the desc_detalhes of the last record entered, how can I do this?

mysql sql mysqli

  • use select limit 1.

  • @Marconi how do I limit only within JOIN? Because in the tb_process table I will show several processes.

  • understood, because it does not make a Function that returns this data instead of making a Join Inner?

  • could know what you represent by nm, in his pattern?

  • @Guilhermelautert represents name.

1 answer

3


What you need is a SubQuery not a 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

This way you are searching within the query tb_processo, a single table record details that belong to the current process and is the last (ORDER BY id_detalhes DESC)

  • Exactly that, thank you.

  • Or it could be a Function as said in the above comment.

  • I need to do the same, as would be this query in Codeigniter?

Browser other questions tagged mysql sql mysqli

You are not signed in. Login or sign up in order to post.