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.
5 Answers
Answers 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; 
Hope this helps...
Answers 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.
Answers 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; Answers 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.
Answers 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; 
0 comments:
Post a Comment