mysql - 什么是正确的SELECT语句？

SELECT *
  FROM notifications
  INNER JOIN COMMENT
    ON COMMENT.id = notifications.source_id
      WHERE idblog IN (SELECT blogs_id
        FROM blogs
        WHERE STATUS = "active")
  INNER JOIN reportmsg
    ON reportmsg.msgid = notifications.source_id
      WHERE uid =: uid
  ORDER BY notificationid DESC
  LIMIT 20;

这里我用INNER JOIN和notifications过滤内容。
但我的问题是，对于第一个comment[即，使用reportmsg]，在将WHERE与INNER JOIN连接之前，我希望将comment与notifications匹配，并且仅将comment的行与notifications.idblog匹配。
为了更好地理解上述代码：

在这里，对于blogs.blogs_id，使用SELECT我只希望blogs.status = "active"中INNER JOIN匹配comment且具有SELECT的行。
带notifications的第二个idblog不需要更改。也就是说，它只过滤blogs.blogs_id。

最佳答案

如下图所示，您只需使用类似的LEFT JOIN将其他表合并到notifications表：

SELECT n.notificationid, n.uid, n.idblog, n.source_id,
       b.blogs_id, b.status,
       c.id,
       r.msgid
       -- ... and the other columns you want
FROM notifications n
LEFT JOIN blogs b ON b.blogs_id = n.idblog AND b.STATUS = "active" AND n.uid =: uid
LEFT JOIN comment c ON c.id = n.source_id
LEFT JOIN reportmsg r ON r.msgid = n.source_id
ORDER BY n.notificationid DESC
LIMIT 20;

希望这有帮助…