mysql - 使用上次对话查询加入用户表

Question

我需要将用户信息表加入聊天消息查询以获取最后回复。

聊天表

mid | sender | receiver| text    | created
  1 |  chrys |     Paul| Hello   | 2015-12-08 20:00
  2 | chrys2 |   Chrys | Hey     | 2015-12-08 20:10

对于最后的回复结果，我正在使用该脚本:

mysql_query("
select * 
    from 
        chat 
      join 
        (select user, max(created) m , COUNT(*) AS msgCount 
            from 
               (
                 (select mid, receiver user, created 
                   from chat 
                   where sender='$login_session' ) 
               union 
                 (select mid, sender user, created
                   from chat 
                   where receiver='$login_session')
                ) t1
           group by user) t2
     on ((sender='$login_session' and receiver=user) or 
         (sender=user and receiver='$login_session')) and 
         (created = m)
   order by created desc
  ");

我的问题是我不知道如何像下面这样加入用户信息表:

id | username | photo_url | age | etcetera.....

一些想法？我所需要的只是从 users_info 表中获取有关用户的信息，例如照片、年龄等发送消息。

Answer 1

根据您的查询，您正在尝试获取已登录用户发送或接收的最后一条聊天消息。

为此，

select c.*
from chat c
join users u on u.id = c.receiver_id or u.id = c.sender_id
where u.id = 'SESSION.User.Id'
order by c.id desc