Problem with INNER JOIN I'm not getting the desired result

Question

I have a table called last_msg, in there i store the last mensage from a private chat between two users, and i update the column from and to when I send a new mensage. I use this table to show a list of mensages like facebook. I also use this table to another things, so i would rather fix the problem described as bellow.

Because of the ON users.user_id = last_msg.from i get data only from who is sending the mensage, this was the best i got... This is my current sql:

SELECT `last_msg`.`msg`, `last_msg`.`from`, `users`.`username`, `users`.`avatar`
FROM `last_msg` 
    INNER JOIN `users` 
        ON `users`.`user_id` = `last_msg`.`from` 
        WHERE `last_msg`.`to` = :user_id_logged OR `last_msg`.`from` = :user_id_logged_2

On the INNER JOIN users I want to get data only from the other user that i'm talking to in the chat, and the data from last_msg can be from both sender and receiver, as the facebook does.

So i tried:

SELECT `last_msg`.`msg`, `last_msg`.`from`, `users`.`username`, `users`.`avatar` 
FROM `last_msg` 
    INNER JOIN `users` 
        ON `users`.`user_id` != :user_logged 
        WHERE (`last_msg`.`to` = :user_logged_2 OR `last_msg`.`from` = :user_logged_3)

But it did not work, it's returning a list of all users in the table users. Any suggestions about how can i fix it?

Answer 1

You can try joining the users table two times, one for the from user and the other for the to user like shown below, also note you need to use LEFT Join in order to get all from and to users.

SELECT `last_msg`.`msg`, `last_msg`.`from`, `FromUser`.`username`, `FromUser`.`avatar`,`ToUser`.`username`,`ToUser`.`avatar`
FROM `last_msg` 
    LEFT JOIN `users` as `FromUser` 
        ON `FromUser`.`user_id` = `last_msg`.`from` 
    LEFT JOIN `users` as `ToUser` 
        ON `ToUser`.`user_id` = `last_msg`.`to` 
        WHERE `last_msg`.`to` = :user_id_logged OR `last_msg`.`from` = :user_id_logged_2";

Updated Code

SELECT `last_msg`.`msg`, `last_msg`.`from`, `users`.`username`, `users`.`avatar` 
FROM `last_msg` 
INNER JOIN `users` 
ON `users`.`user_id` =`last_msg`.`to` 
WHERE `last_msg`.`from`= :user_logged 

UNION 

SELECT `last_msg`.`msg`, `last_msg`.`to`, `users`.`username`, `users`.`avatar` 
FROM `last_msg` 
INNER JOIN `users` 
ON `users`.`user_id` =`last_msg`.`from` 
WHERE `last_msg`.`to`= :user_logged 

Answer 2

This is how to get the messages last sent to you:

SELECT msg, `from` as other_user_id
FROM last_msg
WHERE `to` = :user_logged;

If you also want the messages you last sent:

SELECT msg, `to` as other_user_id
FROM last_msg
WHERE `from` = :user_logged;

You say you store only the one last message per chat. I read this as for users A and B there will only be one row in the table (either the last message A sent to B or the last message B sent to A). So you can just combine the two queries and still show only one last message per chat.

SELECT m.msg, u.user_id, u.username, u.avatar
FROM
(
  SELECT msg, `from` as other_user_id
  FROM last_msg
  WHERE `to` = :user_logged
  UNION ALL
  SELECT msg, `to` as other_user_id
  FROM last_msg
  WHERE `from` = :user_logged
) m
JOIN users u ON u.user_id = m.other_user_id;

An alternative to UNION ALL is a join and CASE WHEN:

SELECT m.msg, u.user_id, u.username, u.avatar
FROM last_msg m
JOIN users u
  ON u.user_id = CASE WHEN :user_logged = m.from THEN m.to ELSE m.from END as other_user_id
WHERE :user_logged IN (m.from, m.to)

This ON clause can also be written as

  ON (u.user_id = m.from AND :user_logged = m.to)
  OR (u.user_id = m.to AND :user_logged = m.from)

or

ON u.user_id IN (m.from, m.to) AND u.user_id <> :user_logged;

by the way. Pick what you like better.

And here is a way to use the user ID parameter only once in the query:

SELECT m.msg, u.user_id, u.username, u.avatar
FROM (select :user_logged as user_id) myself
JOIN last_msg m ON myself.user_id IN (m.from, m.to)
JOIN users u ON u.user_id IN (m.from, m.to) AND u.user_id <> myself.user_id;