CODEWITHSUNDEEP
mysqlsqljoininner-joinparent-child
Andres SK
Andres SK

Reputation: 10982

MySQL query to return a conversation between 2 users

I'm trying to generate a list of messages in a conversation between 2 users. There are 2 relevant tables:

inbox

user

In this example, I'm user ID 1 (Jon), having a chat with user ID 2 (Anna).

SELECT SQL_CALC_FOUND_ROWS
i.id_msg,
i.id_user_from AS from,
i.id_user_to AS to,
u.name,
i.message,
FROM inbox AS i
INNER JOIN user AS u ON (u.id_user = i.id_user_from OR u.id_user = i.id_user_to)
WHERE (i.id_user_from = 1 AND i.id_user_to = 2) OR (i.id_user_from = 2 AND i.id_user_to = 1)
    ORDER BY date_msg DESC

The current problem is that the results are repeated. I'm receiving 2 repeated id_msg values each linked to each user's name, e.g.:

id  | id_from   | id_to | name  | message
1   | 1         | 2     | Jon   | Hi Anna!
1   | 1         | 2     | Anna  | Hi Anna!
2   | 2         | 1     | Jon   | Hello Jon
2   | 2         | 1     | Anna  | Hello Jon

I should be receiving this:

id  | id_from   | id_to | name  | message
1   | 1         | 2     | Jon   | Hi Anna!
2   | 2         | 1     | Anna  | Hello Jon

Any ideas? Thanks!

Upvotes: 0

Views: 672

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181097

If you only want the from user to show up, you don't need to match the to user in the join condition;

SELECT SQL_CALC_FOUND_ROWS
    i.id_msg, i.id_user_from from_id, i.id_user_to to_id,
    u_from.name from_name, u_to.name to_name, i.message
FROM inbox AS i
INNER JOIN user AS u_from ON u_from.id_user = i.id_user_from
INNER JOIN user AS u_to   ON u_to.id_user   = i.id_user_to
WHERE (i.id_user_from = 1 AND i.id_user_to = 2) 
   OR (i.id_user_from = 2 AND i.id_user_to = 1)
ORDER BY date_msg DESC

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271231

This is because your join is using an or. You really have two names, so they should both be in the query. So, I think this might fix your problem:

SELECT SQL_CALC_FOUND_ROWS i.id_msg, i.id_user_from AS from, i.id_user_to AS to,
       ufrom.name as fromname, uto.name as toname, i.message,
FROM inbox i INNER JOIN
     user ufrom
     ON ufrom.id_user = i.id_user_from 
     user uto
     ON uto.id_user = i.id_user_to
WHERE (i.id_user_from = 1 AND i.id_user_to = 2) OR
      (i.id_user_from = 2 AND i.id_user_to = 1)
ORDER BY date_msg DESC;

Upvotes: 1

Related Questions