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!