title: MySQL Query, Join, and Myself, or how I always take the hard way through
body: <p>I'm creating a small forum.</p>
  
  <p>Attempting to run <code>SElECT... JOIN...</code> query too pick up information on the individual posts, plus the last reply (if any). As part of my desire to do everything the hard way, this covers five tables (only columns revelant to this issue are being stated)</p>
  
  <pre>
  commentInfo    referenceID | referenceType | authorID | create
  postit         id | title
  postitInfo     referencePostitID | create | authorID
  user           id | username | permission
  userInfo       referenceUserID | title
  </pre>
  
  <p>So, I run this query <code>SELECT... JOIN...</code> query to get the most recent topics and their last replies.</p>
  
  <pre><code>SELECT DISTINCT
      t1.id, t1.title, t2.create, t2.lastEdit, t2.authorID, t3.username, 
      t4.title AS userTitle, t3.permission, t5.create AS commentCreate, 
      t5.authorID AS commentAuthor, t6.username AS commentUsername, 
      t6.permission AS commentPermission
  FROM rantPostit AS t1
  LEFT JOIN (rantPostitInfo AS t2) 
      ON ( t1.id = t2.referencePostitID)
  LEFT OUTER JOIN (rantUser as t3, rantUserInfo as t4) 
      ON (t2.authorId = t3.id AND t4.referenceUserId = t2.authorId)
  LEFT OUTER JOIN (rantCommentInfo as t5, rantUser as t6) 
      ON (t5.referenceType = 8 AND t5.referenceID = t1.id AND t6.id = t5.authorID)
  ORDER BY t2.create DESC, t5.create DESC
  </code></pre>
  
  <p>Now, this returns the topic posts. Say I have two of them, it returns both of them fine. Say I have eight replies to the first, it will return 9 entries (one each for the topic + reply, and the individual one with no replies). So, I guess my issue is this: I don't know what to do to limit the number of returns in the final <code>LEFT OUTER JOIN</code> clause to just the most recent, or simply strike the least recent ones out of the window.</p>
  
  <p>(Yes, I realize the <code>ORDER BY...</code> clause is messed up, as it'll first order it by the post create date, then by the comment create date. Yes, I realize I could simplify all my problems by adding two fields into postitInfo, lastCommentCreate and lastCommentCreateID, and have it update each time a reply is made, but... I like the hard way.)</p>
  
  <p>So what am I doing wrong?</p>
  
  <p>Or is this such an inane problem that I should be taken 'round the woodshed and beat with a hammer?</p>