Query SQL | Query creation in a chat

Question

I need help to create a query. I'm trying to create a chat where I want to show messages between 2 users.

As I'm making the query, it looks like I just want to show the conversation unilaterally. I want to show the information to both sides.

Here is the table definition:

Create table chat (pid int, id_user int, id_otheruser int, message varchar(45));
insert into chat values
   (1, 1, 9, 'Test chat 1'),
   (2, 1, 9, 'Test chat 2'),
   (3, 9, 1, 'Test chat 3'),
   (4, 1, 10, 'Test chat 4');

And here is my query:

SELECT * FROM `chat` WHERE `id_user` = 1 and `id_otheruser` = 9;

And in a fiddle:

http://sqlfiddle.com/#!9/313f58/1

In the example above I want to show the conversation between ID 1 and ID 9 and I am not able to show the conversation backwards (from ID 9 to ID 1)

Answer 1

SELECT * 
FROM `chat` 
WHERE 
     (`id_user` = 1 and `id_otheruser` = 9)
  OR (`id_user` = 9 and `id_otehruser` = 1);

Alternatively (may perform better):

SELECT * 
FROM `chat` 
WHERE `id_user` = 1 and `id_otheruser` = 9

UNION

SELECT * 
FROM `chat` 
WHERE `id_user` = 9 and `id_otheruser` = 1;

I further suggest adding a datetime field to this table, to preserve order and timing of the messages.