What would be the correct SELECT answer 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 notificationswith commentand reportmsg; then filtering the content with WHERE.

But my problem is that for the first INNER JOIN[i.e. c comment] before joining notificationswith, commentI want to match notifications.idblogwith blogs.blogs_idand SELECTonly those lines where blogs.status = "active".

For a better understanding of the code above:

Here for INNER JOINwith commentI want SELECTonly those lines in notificationswhose idblogmatches blogs.blogs_idand has status = "active".

The second INNER JOINc reportmsgdoes not need to be changed. Ie, it only filters through uid.

Answer 1

, , LEFT JOIN :

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

/ , , WHERE :

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

, B.STATUS = 'active' , . ( a LEFT JOIN, , )

, , *, , .

Answer 3

, , INNER JOIN, LEFT JOIN . LEFT JOIN , , WHERE . , LEFT JOIN, :

  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

, . , , .

, INNER JOINs , .

JOIN, BOTH reportmsg.

, , LEFT JOINs , , :

   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 

:

• , →
• notifications.notificationid, comment.id → id
• notificationid, source_id →
• idblog, notificationid →

Currently, you pretty much have to search every identifier field every time you want to use it.

Answer 5

You should change your request 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;