Query with SELECT and SUB SELECT in Mysql

Asked

Viewed 6,988 times

2

I have a question about SQL, never worked with sub selects and ended up getting lost with it.

My SQL:

SELECT CLI.id, CLI.nome, CLI.senha, CLI.email, CLI.cpf, CLI.celular, CLI.data_nasc, CLI.genero, CLI.data_cadastro, CLI.status, CLI.id_socket, ATEN.mensagem, ARQ.nome AS foto, ATEN.data_mensagem
FROM ut_clientes AS CLI
LEFT JOIN ut_arquivos AS ARQ ON (ARQ.id_tipo = CLI.id AND ARQ.tipo = "ut_clientes")
INNER JOIN ut_atendimentos AS ATEN ON (ATEN.id_usuario_envio = CLI.id)
WHERE ATEN.id_usuario_envio != 1
GROUP BY CLI.id 
ORDER BY ATEN.data_mensagem
DESC

Well, what I would like to do is group the messages according to the customer ID and bring only the last message recorded in the database according to the data_message.

I have tried several ways but always the last that is displayed is the first message inserted in the bank.

mysql sql database

2 answers

1


Use the clause EXISTS together with the NOT to select only the message with the longest date:

SELECT cli.id,
       cli.nome,
       cli.senha,
       cli.email,
       cli.cpf,
       cli.celular,
       cli.data_nasc,
       cli.genero,
       cli.data_cadastro,
       cli.status,
       cli.id_socket,
       aten.mensagem,
       arq.nome AS foto,
       aten.data_mensagem
  FROM ut_clientes AS cli
       LEFT JOIN ut_arquivos AS arq ON arq.id_tipo = cli.id
                                   AND arq.tipo = "UT_CLIENTES"
       LEFT JOIN ut_atendimentos AS aten ON aten.id_usuario_envio = cli.id
 WHERE aten.id_usuario_envio <> 1
   AND NOT EXISTS(SELECT 1
                    FROM ut_atendimentos AS aten2
                   WHERE aten2.id_usuario_envio = cli.id
                     AND aten2.data_mensagem > aten.data_mensagem)
 GROUP BY cli.id
 ORDER BY aten.data_mensagem DESC

Subqueries with EXISTS or NOT EXISTS

If a subquery Returns any Rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.

Or in free translation:

If the subquery returns any line, EXISTS will be VERDADEIRO, and NOT EXISTS will be FALSO.

  • That’s exactly what it was, thank you very much!

  • @Tiagopaza do not forget to mark the answer as chosen if you have answered your question

0

In your case it would look something like this:

SELECT CLI.id, CLI.nome, CLI.senha, CLI.email, CLI.cpf, CLI.celular, CLI.data_nasc, CLI.genero, CLI.data_cadastro, CLI.status, CLI.id_socket, ATEN.mensagem, ARQ.nome AS foto, ATEN.data_mensagem
FROM ut_clientes AS CLI
LEFT JOIN ut_arquivos AS ARQ ON (ARQ.id_tipo = CLI.id AND ARQ.tipo = "ut_clientes")
INNER JOIN (
    SELECT a.data_mensagem, a.mensagem
    FROM ut_atendimentos a
    WHERE a.id_usuario_envio = CLI.id
    ORDER BY a.data_mensagem DESC
    LIMIT 1
) AS ATEN
WHERE ATEN.id_usuario_envio != 1
GROUP BY CLI.id;

Browser other questions tagged mysql sql database

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