SQL with SELECT and SUBSELECT to set up a chat

0

Hello everyone, I'm building a SQL Server database, and I'm using SQL Server 2005 to create a user-defined chat. I have a list of all the active conversations, but I have a problem, my SQL is bringing the last message according to id of the Client, that is if my message is the last, it does not display, and I need to put a COUNT if there are new messages if the message_message is > that the current message_message for me to bring the notifications of how many messages are.

NOTE: The table that stores the messages: link

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, COUNT(ATEN.mensagem) AS novas_mensagens, SUM(COMP.valor) AS valor_total, COMP.data AS ultima_compra, ARQ.nome AS foto, ATEN.data_mensagem
FROM ut_clientes AS CLI

LEFT JOIN ut_compras AS COMP ON COMP.id_cliente = CLI.id
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
  AND NOT EXISTS(
    SELECT ATEN.id_usuario_envio
    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

I do not know if the idea was clear, but anything is just asking me, would appreciate if someone could give a light kkk

Thanks!

    
asked by anonymous 25.05.2018 / 17:51

0 answers