MSYQL SELECT and group conversations by user and show last message

Question

I have a user table and a message table.

In the message table (named messaggi) there are msg_to and msg_from, their message (msg_text) and msg_date (which is a date and time).

I'm trying to achieve to have a list of:

1. all users from the utenti table

2. For each user where a message was SENT or RECEIVED the last should show

3. Hide message from people who are not self (in this case id_utente 1 = self).

This is what I came up with but I keep on either getting all messages or double users, and so forth...

SELECT CONCAT(LEFT(u.fname, 1), LEFT(u.lname, 1)) AS iniziali,
       u.email,
       u.color,
       CONCAT(u.fname, " ", u.lname) AS full_name,
       MAX(m.msg_date) AS msg_date,
       m.msg_text
FROM utenti u
     INNER JOIN messaggi m ON m.msg_to = u.id_utente
WHERE m.msg_to = 1
GROUP BY m.msg_to,
         m.msg_from
UNION
SELECT CONCAT(LEFT(u2.fname, 1), LEFT(u2.lname, 1)) AS iniziali,
       u2.email,
       u2.color,
       CONCAT(u2.fname, " ", u2.lname) AS full_name,
       "",
       ""
FROM utenti u2
WHERE u2.id_utente NOT IN
(
    SELECT id_utente
    FROM utenti u
         INNER JOIN messaggi m ON m.msg_to = u.id_utente
    WHERE m.msg_to = 1
          AND u.id_utente = 1
    GROUP BY m.msg_to,
             m.msg_from
);

Here is a Fiddle:

http://sqlfiddle.com/#!9/106319/1

The output I want to have should be:

| iniziali |       email |  color |    full_name |            msg_date |       msg_text |
|----------|-------------|--------|--------------|---------------------|----------------|
|       BV |  [email protected] | (null) |   Bill Villa | 2018-04-20 12:29:20 | Msg 2 (1 to 2) |
|       MG |  [email protected] | (null) |    Max Gazze | 2018-04-09 14:59:39 | Msg 1 (3 to 1) |
|       JB |  [email protected] | (null) |    Jack Blue |                     |                |

In this case, me being id#1 I only see the messages sent FROM ME and TO ME, and for each user I only see the last one received or sent. Like WhatsApp, Facebook Messanger, Telegram, etc... You see the contact and the last message sent/received for each one.

Messages to and from other users (i.e: user 2 to user 3) are not shown.

As you can see I DO NOT see myself (id#1) in the users list and for Jack Blue I only see his name e no message, as no message was ever sent from/to user 1 and 4. So I end up with a list of users and for each of them I see the most recent message (sent or received) and where no message I only see the user with empty msg_text and msg_date

Answer 1

Here is my attempt at this, I make use of a union and the first part is for those users that has messages from/to id 1 and the second part is for those that has not.

SELECT CONCAT(LEFT(u.fname, 1), LEFT(u.lname, 1)) AS iniziali,
   u.email,
   u.color,
   CONCAT(u.fname, " ", u.lname) AS full_name,
   u.id_utente,
   m.msg_date,
   m.msg_text
FROM utenti u, messaggi m
WHERE (m.msg_to = u.id_utente OR m.msg_from = u.id_utente)
AND (m.msg_to = 1 and m.msg_from != 1 OR m.msg_to != 1 and m.msg_from = 1)
AND m.msg_date = (SELECT MAX(m2.msg_date) FROM messaggi m2 WHERE (m2.msg_to = u.id_utente AND m2.msg_from = 1) OR (m2.msg_from = u.id_utente AND m2.msg_to = 1))
UNION ALL
SELECT CONCAT(LEFT(u.fname, 1), LEFT(u.lname, 1)) AS iniziali,
   u.email,
   u.color,
   CONCAT(u.fname, " ", u.lname) AS full_name,
   u.id_utente,
   '',
   ''
FROM utenti u
WHERE NOT EXISTS (SELECT * FROM messaggi m WHERE (m.msg_to = u.id_utente AND m.msg_from = 1) OR (m.msg_from = u.id_utente AND m.msg_to = 1))
AND u.id_utente != 1
ORDER BY msg_date DESC