If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Need Help Joining these Queries

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

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `user_name` FROM `private_replies` WHERE `thread_id` = `private_t' at line 10

Edit, I think my MySQL version is 5.5.29-log

Last edited by evenstar7139; 01-29-2013 at 05:51 PM.

The better I get at programming, the more I appreciate arrays.Handy dandy things they are.

#1 If no replies are found, is there a way to have the subquery return the sender_id for the thread? (The whole idea is it returns whoever made the last post. Obviously that's the thread creator if there are no replies.)

#2 Do you think this query will be faster than doing two separate queries?

The better I get at programming, the more I appreciate arrays.Handy dandy things they are.

#1. I might just leave that to the application code to figure out (a simple if/else should be able to handle that).

#2. All things being equal (which they never are, of course), I'd tend to favor the single query when feasible as far as performance goes, but assuming you keep the DB connection open between queries, there's probably not all that much additional overhead, so I'd probably not sweat unless/until you determine you are having a performance bottleneck in that particular area. Sometimes I opt for maintainability over raw performance, in which case I might run separate queries when things start to get too convoluted.

In that type of sub-query usage, it can only select one column -- or multiple columns concatenated or otherwise combined into a single field. In other words, the sub-query is being used to populate a single column in the result rows.

Before we try to figure this out, if it's not to late in the game for this, I'd suggest a DB redesign that would get rid of this problem: use one table for all of the messages, so that the original message text is treated the same as any/all replies -- it's essentially just the first reply to that thread.

I changed the structure of the tables so message_threads only houses information about the thread and every message goes in message_messages. I also changed my query a lot and it's not working like I want. It's returning one result for every reply, instead of one result for every thread. So, if a thread has say 5 replies, it will return 5 results for that thread. It should only return one result and that result should just have information about the thread's last reply.

*scratches head* I sent messages in between my main account and my tester account. On my main it's correctly showing one listing per thread. On my tester it's showing one listing per reply. Here, look: