MySQL Select JOIN 3 Tables

Question

I have three main tables:

tblUsers: usrID usrFirst usrLast 1 John Smith 2 Bill Jones 3 Jane Johnson pm_data: id date_sent title sender_id thread_id content 2 2009-07-29 18:46:13 Subject 1 1 111 Message 2! 3 2009-07-29 18:47:21 Another Subject 1 222 Message 3! pm_info: id thread_id receiver_id is_read 1 111 2 0 2 111 3 0 3 222 2 0 4 222 3 0 

Essentially, I'm trying to create a mailbox.

So, if usrID 2 (Bill Jones) opens his mailbox, he will see that he is 2 unread (hence the "is_read" column) (threads # 111 and # 222).

Basically, I need to know how to set up a SELECT statement for the JOIN of all three tables (the relationship between pm_data and pm_info leads to message information, and the relationship between tblUsers and pm_data leads to the "display name" of the sender) to show the latest one (by label ??) thread on top.

So we will see something like this:

 <?php $usrID = 2; ?> <table id="messages"> <tr id="id-2"> <td> <span> From: John Smith </span> <span>2009-07-29 18:47:21</span> </td> <td> <div>Another subject</div> </td></tr> <tr id="id-1"> <td> <span> From: John Smith </span> <span>2009-07-29 18:46:13</span> </td> <td> <div>Subject 1</div> </td></tr> </table> 

Hope this makes sense! Thanks for any help!

EDIT: Here is my final answer:

I took the lc-tip and made the connection between the two tables based on id (added a column named "message_id" on pm_info).

Then, modifying the MySQL statement a bit to come up with this:

 SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id FROM pm_info INNER JOIN pm_data ON pm_info.message_id = pm_data.id INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID WHERE pm_data.date_sent IN(SELECT MAX(date_sent) FROM pm_data WHERE pm_info.message_id = pm_data.id GROUP BY thread_id) AND pm_info.receiver_id = '$usrID' ORDER BY date_sent DESC 

It seems to work for me (for now).

Answer 1

You will need two connections. Something like the following should start you up (although I don't understand the 100% relationship between pm_data and pm_info ):

 SELECT pm_info.is_read, sender.usrFirst + ' ' + sender.usrLast as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id FROM pm_info INNER JOIN pm_data ON pm_info.thread_id = pm_data.thread_id INNER JOIN tblUsers AS sender ON pm_data.sender_id = tblUsers.usrID WHERE pm_info.receiver_id = @USER_ID /*in this case, 2*/ ORDER BY pm_data.date_sent DESC 

I assume that the relation between pm_data and pm_info is a stream identifier. If this is not the case, you should be able to customize above everything you need. I also sorted by send date, but it will not support threads together . I'm not sure if you want to keep them together or not, since you have formulated your question.

---

If you want to keep the threads together , you will need a more complex query:

 SELECT pm_info.is_read, sender.usrFirst + ' ' + sender.usrLast as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id FROM pm_info INNER JOIN pm_data ON pm_info.thread_id = pm_data.thread_id INNER JOIN tblUsers AS sender ON pm_data.sender_id = tblUsers.usrID INNER JOIN (SELECT thread_id, MAX(date_sent) AS max_date FROM pm_data GROUP BY thread_id) AS most_recent_date ON pm_data.thread_id = most_recent_date.thread_id WHERE pm_info.receiver_id = @USER_ID /*in this case, 2*/ ORDER BY most_recent_date.max_date DESC, pm_data.thread_id, pm_data.date_sent DESC 

This query uses a subheading to find the most recently modified date for each stream, and then sorted first.

Answer 2

To get a list of messages for the user along with who and when he sent him, you can use the following query:

 select s.usrFirst + ' ' + s.usrLast as SenderName, m.Title, m.DateSent, i.IsRead from tblUsers r inner join pm_info i on r.receiver_id = i.receiver_id inner join pm_data m on i.thread_id = m.thread_id inner join tblUsers s on m.sender_id = s.userID where r.usrid = @id 

This takes advantage of the fact that you can join the table for yourself (here tblUsers displayed twice: once for the recipient and again for the sender).

If you want to see only unread messages, you can put and i.IsRead = 0 in the where clause.