I am trying to construct a query that deals with threads and replies.
It involves two tables: one for private message threads, the other for private message replies. When a user first messages another user, a thread is created. The thread information is stored in private_threads and it contains the thread ID number, sender's ID number, message title, message content, and a couple other things. Any subsequent correspondence between the two users is added as a reply to this thread. These subsequent messages go into private_replies and they contain the author's ID number, a unix timestamp,the message content, and a couple other things.
When users go to their inbox I want them to see a table that lists the threads along with who last replied to them. This is why I need the join query. I'm thinking the query would tell MySQL something like "Find all private message threads received by this user and pull them up. Then find the latest reply for each thread. If a reply is found, get the author's ID number. If not, then return the thread creator's ID number."
This is the query I have so far. It does't do any joins yet:
SELECT `thread_id`, `title`, `message`, `sender_id`, `timestamp`, `read_by_receiver`, `read_by_sender` FROM `private_threads` WHERE `receiver_id` = "1" ORDER BY `timestamp` DESC
Does this make sense?
The better I get at programming, the more I appreciate arrays. Handy dandy things they are.