MySQL Select JOIN 3 Tables

Question

Hi All,

I've got sort of a 'tricky' one here-- well, maybe not.

I have three basic 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, what I am trying to do is create an inbox.

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

Basically, I need to know how to set up my SELECT statement to JOIN all three tables (the relationship between pm_data and pm_info brings about the message info, while the relationship between tblUsers and pm_data brings about the 'display name' of the sender), to show the most recent (by timestamp?) thread on top.

Thus, we would 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>

Hopefully this makes sense! Thanks for any help!

EDIT: Here's my final answer:

I took lc's advice, and made the relationship between the two tables based on id (added a column called 'message_id' to pm_info).

Then, tweaked the MySQL statement around a little 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

This seems to work for me (so far).

Answer 1

To get the list of messages for a user along with who and when they sent it, 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 a table to itself (here, tblUsers shows up twice: Once for the recipient, and again for the sender).

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

Answer 2

You'll need two joins. Something like the following should get you started (although I don't 100% understand the 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'm assuming the relation between pm_data and pm_info is the thread id. If it isn't, you should be able to adjust the above to whatever you need. I've also sorted by date sent here, but it won't keep the threads together. I'm not sure if you want to keep them together or not from the way you've phrased your question.

---

If you want to keep threads together, you'll need a more complicated 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 subselect to find the most recent modified date for each thread, then sorts by this first.