What Would be the Correct SELECT Statement for This?

Question

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:

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.

Answer 1

As you can see from the image below, you can just need to merge other tables to notifications table using LEFT JOIN like that:

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;

Answer 2

There's no need/reason to filter before the second join because you only use inner joins and then the order of joins and WHERE-conditions don't matter:

SELECT n.*, c.*, r.*
FROM notifications AS n
JOIN COMMENT  as c 
  ON n.source_id = c.id
LEFT JOIN blogs as b
  ON n.idblogs = b.blogs_id
 AND B.STATUS = 'active'
JOIN reportmsg AS R
  ON n.source_id = r.msgid 
WHERE uid =: uid
ORDER BY notificationid DESC
LIMIT 20

You can switch the order of joins, you can move B.STATUS = 'active' into the join-condition, but all queries will return the same result. (After the edit it's a LEFT JOIN, of course now the result differs)

And of course you shouldn't use *, better list only the columns you actually need.

Answer 3

if query optimizer does its work, it does not matter where you put filtering statement in INNER JOIN case but in the LEFT JOIN it has effects. Putting filtering statement in LEFT JOIN conditions cause table filtered at first and joined after while putting filtering statement in WHERE clause will filter results of join. Hence, if you want to use LEFT JOIN your query must look like:

  SELECT nt.*
  FROM notifications nt
  LEFT JOIN Blogs bg on nt.blogs_id = bg.blogs_id and bg.STATUS = "active"
  LEFT JOIN COMMENT cm ON cm.id = nt.source_id         
  LEFT JOIN reportmsg rm ON rm.msgid = nt.source_id
  WHERE uid =: uid
  ORDER BY nt.notificationid DESC
  LIMIT 20;

Answer 4

It's very unclear what you are after here.. while your table diagram is useful, you should really supply some sample data and an expected result even if it is just a couple of dummy rows for each table.

Queries work row by row, both INNER JOINs are applied to the same notification row and non-matching rows are discarded.

Any filter applies to both JOIN and any returned rows must have a match in BOTH comment and reportmsg.

Perhaps you want two LEFT JOINs that can apply different filters and guessing from the table names perhaps it could look like this:

   SELECT *
     FROM notifications n
LEFT JOIN blogs b
       ON n.blogId = b.blogs_id
LEFT JOIN comment c
       ON c.id = n.source_id
      AND b.status = "Active"
LEFT JOIN reportmsg rm
       ON rm.msgid = n.source_id
    WHERE n.uid =: uid
      AND (c.id IS NOT NULL OR rm.msgid IS NOT NULL)
 ORDER BY n.notificationid DESC
    LIMIT 20 

You also should work on your naming convention:

• notifications, comment -> pick either plural or singular table names
• notifications.notificationid, comment.id -> pick adding table name to id
• notificationid, source_id -> pick underscore or no separation
• idblog, notificationid -> pick prepending or appending id

Currently you pretty much have to look up every id field every time you want to use one.

Answer 5

You should change your query to this:

SELECT * 
FROM notifications 
INNER JOIN comment ON comment.id = notifications.source_id 
INNER JOIN reportmsg ON reportmsg.msgid=notifications.source_id 
LEFT JOIN blogs ON notifications.idblog = blogs.blogs_id
WHERE blogs.status = 'active'
ORDER BY notificationid DESC 
LIMIT 20;