我需要为下面的查询添加一个连接,该连接将messages_message表连接到messages_threads表,并从messages_messages中最近的行返回'body'和'time'字段,其中messages_message。Threadid = messages_threads.id.
对不起,这太令人困惑了,我不知道该怎么表达!这是我当前的查询-它正确地提取除了'body'和'time'字段的所有内容。
SELECT
messages_threads.id AS threadid, messages_threads.name AS recipientsname,
senderid, username AS sendername, pictures.url AS senderpicture
FROM
messages_recipients
JOIN messages_threads
ON messages_threads.id = messages_recipients.threadid
JOIN users
ON users.id = messages_threads.senderid
JOIN pictures
ON messages_threads.senderid = pictures.userid
AND pictures.profile = 1
WHERE messages_recipients.userid = $userid
您可以添加messages_message表的子查询,它将为每个线程返回1行。例如:
SELECT
messages_threads.id AS threadid, messages_threads.name AS recipientsname,
senderid, username AS sendername, pictures.url AS senderpicture
FROM
messages_recipients
JOIN messages_threads ON messages_threads.id = messages_recipients.threadid
JOIN users ON users.id = messages_threads.senderid
JOIN pictures ON messages_threads.senderid = pictures.userid AND pictures.profile = 1
JOIN ( SELECT threadid, MAX(id) AS postid FROM messages_message GROUP BY threadid ) t ON message_threads.id = t.threadid
JOIN messages_message ON t.threadid = messages_message.threadid AND t.postid = messages_message.id
WHERE messages_recipients.userid = $userid
我想你也需要按身体和时间分组…