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?

Answer 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.

Answer 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.