this is driving me mad, been trying to formulate one SQL query for 2 days now, please someone put me out of my misery!
Doing a query on 2 tables:-
messages table.
ID // key field, int
threadID // key field of thread this message is in
title, body, etc. // stuff
unreadmessages table
ID // key field
msgID // corresponds to the ID of a message record
threadID // key field of thread this message is in
userID // userID - indicates the user hasnt yet read the associatde message record.
So thats a table of messages, added by various users. When a message is added, an unreadmessages record is created for all users apart from the one who has posted the new message.
The query needs to be as follows:-
Given a threadID and the userID of the user logging in, return all message records with that threadID. IF there is a corresponding unreadmessages record for that messageID and userID, return a value (indicating unread message) otherwise return value indicating message has already been read by this user, eg \'null\'.
sounds simple enough but I just cant get it to work!
At first I thought it\'d need a LEFT OUTER JOIN, but that would get all messages, not just for this thread. Maybe I need to nest 2 SELECT statements? Get a resultset of all messages (and get value from unreadmessages when there is a corresponding record), then produce a final table where only records with the right threadID are returned.
hope I\'ve made that clear, I\'ve been stuck with this for a couple of days and a very tight deadline is looming up and I\'m going crazy-daisy.
cheers