title: Get data from two tables depending on data from one in a single query
body: <p>I'm making a simple forum, and in the listing of the threads i want the classical &quot;last post by (user) at (date)&quot; to show. I'm using only one table to contain all the posts in the forum, where posts with the parentPost-field not equal to null are replies to a thread.</p>
  <p>These are the tables and necessary fields i need data from:</p>
  forumPosts
  <ul>
  <li><strong>id</strong>: integer, primary key</li>
  <li><strong>title</strong>: string value</li>
  <li><strong>date</strong>: string value</li>
  <li><strong>parentPost</strong>: integer (This field = ID of the initial post in the thread. If it's NULL, the post is an &quot;initial thread post&quot;)</li>
  <li><strong>author</strong>: integer, foreign key to the users table</li>
  <li><strong>countAnswers</strong>: integer</li>
  <li><strong>lastPost</strong>: integer, (This field = the ID of the last post which was replied to it. Only used when the parentPost-field = NULL)</li>
  </ul>
  users
  <ul>
  <li><strong>userid</strong>: integer, primary key</li>
  <li><strong>firstName</strong>: string value</li>
  <li><strong>lastName</strong>: string value</li>
  </ul>
  <p>As of now, i have this query:</p>
  <pre><code>SELECT forumPosts.id, forumPosts.title, forumPosts.date, forumPosts.author, forumPosts.countAnswers, users.firstName, users.lastName 
  FROM forumPosts 
  INNER JOIN users ON forumPosts.author = users.userid 
  WHERE forumPosts.parentPost IS NULL ORDER BY id DESC;
  </code></pre>
  <p>Returns all I need (ID, Title, Date, Author and how many replies it has), except for the date and author of the last posted reply.</p>
  <p>In addition, I'd like to fetch the following:</p>
  <p>The date from the last post in the thread, that is the post with the highest ID <em>and</em> parentPost = forumPosts.id. I'd also want to fetch the firstName and lastName from the user that posted this reply.</p>
  <p>I've been fooling around now for a long time with different joins and derived tables and such, but I can't seem to get this right. Ideally, i'd want a table returned with the following fields:</p>
  <ul>
  <li>id</li>
  <li>title</li>
  <li>date</li>
  <li>firstName</li>
  <li>lastName</li>
  <li>countAnswers</li>
  <li>lastReplyDate</li>
  <li>lastReplyFirstName</li>
  <li>lastReplyLastName</li>
  </ul>
  <p>To me, it seems possible, but I obviously can't make it.</p>
  <p>I hope you understood my somewhat complex question, and i thank you a lot for all answers.</p>
  <h3>The sollution</h3>
  <pre><code>SELECT        forumPosts.id, forumPosts.title, forumPosts.date, forumPosts.author, forumPosts.countAnswers, users.firstName, users.lastName, 
                       lastPost.date AS lastPostDate, lastUser.firstName AS lastPostFirstName, lastUser.lastName AS lastPostLastName
  FROM            forumPosts AS lastPost INNER JOIN
                       users AS lastUser ON lastPost.author = lastUser.userid INNER JOIN
                       forumPosts INNER JOIN
                       users ON forumPosts.author = users.userid ON lastPost.id = forumPosts.lastPost
  WHERE        (forumPosts.parentPost IS NULL)
  ORDER BY forumPosts.id DESC
  </code></pre>
  <p>That worked. So typical, finding my own answer a minute after i've spent a lot of time writing a post here :-\</p>