mysql - 2 左加入工会

Question

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

我想获取这些人的所有对话，并显示他们的全名、消息以及他们发送该消息的时间，但我的查询没有正确返回他们的姓名，这是我的查询:

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

此查询的结果将是:

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

但我想要的输出是:

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

Answer 1

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 = 1 AND c.another_user = 9)
   OR (c.users_id = 9 AND c.another_user = 1)
ORDER BY time_sent ASC