What Would be the Correct SELECT Statement for This?

Viewed 368
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;

Here I am INNER JOINing notifications with comment and reportmsg; then filtering content with WHERE.

But my problem is that for the first INNER JOIN [i.e, with comment], before joining notifications with comment, I want to match notifications.idblog with blogs.blogs_id and SELECT only those rows where blogs.status = "active".

For better understanding of the code above:

ER diagram

Here, for INNER JOIN, with comment I want to SELECT only those rows in notifications whose idblog matches blogs.blogs_id and has status = "active".

The second INNER JOIN with reportmsg needs not to be altered. I.e, it only filters through uid.

5 Answers
Related