title: MySQL Select JOIN 3 Tables
body: <p>I have three basic tables:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Essentially, what I am trying to do is create an inbox.  </p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>Thus, we would see something like this:</p>
  
  <pre><code>&lt;?php  $usrID = 2;  ?&gt;
  
  &lt;table id="messages"&gt;
    &lt;tr id="id-2"&gt;
    &lt;td&gt;
     &lt;span&gt;
       From: John Smith
      &lt;/span&gt;
      &lt;span&gt;2009-07-29 18:47:21&lt;/span&gt;
    &lt;/td&gt;
   &lt;td&gt;
   &lt;div&gt;Another subject&lt;/div&gt;
   &lt;/td&gt;&lt;/tr&gt;
  &lt;tr id="id-1"&gt;
   &lt;td&gt;
     &lt;span&gt;
       From: John Smith
     &lt;/span&gt;
     &lt;span&gt;2009-07-29 18:46:13&lt;/span&gt;
  &lt;/td&gt;
   &lt;td&gt;
     &lt;div&gt;Subject 1&lt;/div&gt;
   &lt;/td&gt;&lt;/tr&gt;
   &lt;/table&gt;
  </code></pre>
  
  <p>Hopefully this makes sense!  Thanks for any help!</p>
  
  <p>EDIT:  Here's my final answer:</p>
  
  <p>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).</p>
  
  <p>Then, tweaked the MySQL statement around a little bit to come up with this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This seems to work for me (so far).  </p>