title: Building SQL query to join three tables to get messaging info
body: <p>I have the following three tables for a messaging system:</p>
  
  <pre><code>`messaging_messagethread`
  - id
  - subject
  - initiator_id # who creates the thread
  - recipient_id
  
  `messaging_message`
  - id
  - thread_id
  - content
  - timestamp
  - sender_id
  
  `messaging_messagestatus` # a status will be created for each recipient of a message
  - id
  - message_id
  - recipient_id
  - status
  </code></pre>
  
  <p>Given a user, I need to build a query to get the following:</p>
  
  <ul>
  <li>Show Thread ID (distinct), </li>
  <li><code>content</code> and <code>timestamp</code> of the most recent message in that thread</li>
  <li>Remove any threads with the most recent message <code>status='deleted'</code>. </li>
  </ul>
  
  <p>Here is what I have so far: </p>
  
  <pre><code>SELECT DISTINCT thread.id as thread_id, timestamp.timestamp 
      FROM messaging_messagethread thread 
  INNER JOIN 
      (SELECT thread_id, MAX(timestamp) as timestamp
        FROM messaging_message GROUP BY thread_id) timestamp
        ON thread.id = timestamp.thread_id
  WHERE initiator_id = 4 OR thread.recipient_id = 4 ORDER BY timestamp.timestamp DESC
  </code></pre>
  
  <p>This gives me distinct thread id's ordered by most recent timestamp. (My first of three points). How would I build the entire query?</p>