tbl\_chat:

```
message_id  message_text    users_id  another_user   time_sent
1           'hi'                9        1           2014-10-13 00:10:32   
2           'hello'             1        9           2014-10-13 00:12:32
3           'good morning'      9        1           2014-10-13 00:12:34
4           'good night'        9        1           2014-10-13 00:14:02
5           'LOL'               1        9           2014-10-13 00:14:05

```

tbl\_usersinfo:

```
users_id    users_fname     users_lname
1            ben               ten
9            son               goku

```

I want to get all the conversation of these people, and show their full name, message, and the time that they sent that message, but my query doesn't return the name of them correctly, here is my query:

```
SELECT CONCAT_WS(' ',i.users_fname, i.users_lname) AS full_name, c.message_text,c.time_sent,c.message_id
FROM tbl_chat AS c
LEFT JOIN tbl_usersinfo AS i ON i.users_id = c.another_user
WHERE c.users_id = 1
UNION
SELECT CONCAT_WS(' ',i.users_fname, i.users_lname) AS full_name, c.message_text,c.time_sent,c.message_id
FROM tbl_chat AS c
LEFT JOIN tbl_usersinfo AS i ON i.users_id = c.users_id
WHERE c.users_id = 9
ORDER BY time_sent ASC

```

the result of this query will be:

```
full_name  message_text        time_sent             message_id
 son goku    'hi'               2014-10-13 00:10:32     1
 son goku    'hello'            2014-10-13 00:12:32     2 
 son goku    'good morning'     2014-10-13 00:12:34     3
 son goku    'good night'       2014-10-13 00:14:02     4
 son goku    'lol'              2014-10-13 00:14:05     5

```

but my desired output is:

```
full_name  message_text        time_sent             message_id
son goku    'hi'               2014-10-13 00:10:32    1
ben ten     'hello'            2014-10-13 00:12:32    2 
son goku    'good morning'     2014-10-13 00:12:34    3
son goku    'good night'       2014-10-13 00:14:02    4
ben ten     'lol'              2014-10-13 00:14:05    5

```