mysql - JOIN 查询产生不需要的结果

Question

我知道查询正在执行它应该如何但是它没有执行我想要的方式。当前查询找到所有包含消息和显示在提供的 unix_timestamp 之后的所有消息。

此查询的问题是我需要显示 application_conversation 中的所有字段，无论是否有可用消息，例如当前查询产生以下结果:

conversation_id | conversation_participant_one | conversation_participant_two | conversation_last_message | conversation_last_message_date | message_id | message_conversation_id | message_sender_id | message_data | message_created_at
       1                      33                             34                      "How are you?"               2016-04-25 08:59:59            1                 1                       33          "How are you?"  2016-04-25 08:59:59    

以上结果是我当前查询提供的结果，但是 application_conversation 表中还有另一行匹配用户 33 和 36。您可以在下面看到我希望从查询中返回哪些结果的示例。 (基本上，消息不存在的任何字段都将为空)。

conversation_id | conversation_participant_one | conversation_participant_two | conversation_last_message | conversation_last_message_date | message_id | message_conversation_id | message_sender_id | message_data | message_created_at
       1                      33                             34                      "How are you?"               2016-04-25 08:59:59            1                 1                       33          "How are you?"  2016-04-25 08:59:59
       2                      33                             36                           NULL                    1970-01-01 00:00:00           NULL              NULL                    NULL              NULL              NULL         

这是我目前正在使用的查询:

SELECT C.*, M.*
FROM `application_conversation` C
INNER JOIN `application_conversation_messages` M ON M.message_conversation_id = C.conversation_id
WHERE (C.conversation_participant_one = 33 OR C.conversation_participant_two = 33)
AND C.conversation_created_at > FROM_UNIXTIME(0)
AND M.message_created_at > FROM_UNIXTIME(0)
ORDER BY M.message_created_at;

现在这里的一个问题是我还必须对消息的 message_created_at 字段进行排序(如果它确实存在)，这样我就不会提取早于请求的时间戳的消息客户。在此查询中，这意味着消息必须存在。

我怎样才能不要求消息存在，而是在消息确实存在的情况下应用 filter(M.message_created_at > FROM_UNIXTIME(0))，如果仍然返回 NULL 值该消息不存在。

我正在使用 MySQL。与 XAMPP 包捆绑在一起。不是 SQLServer。

Answer 1

使用 LEFT JOIN，并将条件放在 ON 子句中的 application_conversation_messages 表上。

SELECT C.*, M.*
FROM `application_conversation` C
LEFT JOIN `application_conversation_messages` M ON M.message_conversation_id = C.conversation_id AND M.message_created_at > FROM_UNIXTIME(0)
WHERE 33 IN(C.conversation_participant_one,C.conversation_participant_two)
AND C.conversation_created_at > FROM_UNIXTIME(0)
ORDER BY M.message_created_at;

参见 http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/了解 INNER JOIN 和 LEFT JOIN 之间的区别。