Tutorial :Problem writing this query in mysql (marking read messages in a forum)



Question:

Hey. i am writing a forum, and i have this table that marks messages a specific user read:

`read_messages`(`message_id`,`user_id`)  

a simplified version of the messages table:

`messages`(`id`,`forum_id`,`author_id`)  

now, i want to be able, when retrieving the message data from the database for a given forum, to add a variable that will tell me if the current user has read that message or not. i know how to do this with 2 queries (1st i retrieve all messages, then i check for each of them if the user has read them), but no clue as to how to join them together. any ideas?


Solution:1

SELECT messages.*, read_messages.id as read_id  FROM messages   LEFT OUTER JOIN read_messages      ON ( messages.id = read_messages.message_id AND read_messages.user_id  = [ USER ID ] )  

If read_id is returned as a number > 0 rather than NULL, then the message has been read because there is a corresponding record in read_messages for that user.


Solution:2

It sounds like you're after an Outer Join, check out the Outer Joins Section for the syntax, which allows you to select all values from one table and NULLS where the values don't exist.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »