title: MYSQL subquery SELECT in JOIN clause
body: <p>Ok... well I have to put the subquery in a <code>JOIN</code> clause since it selects more than one column and putting it in the <code>SELECT</code> clause does not allow that as it gives me an error of an operand.
  Anywho, this is my query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT 
      c.id, 
      c.title, 
      c.description, 
      c.icon, 
      p.id as topic_id, 
      p.title AS topic_title, 
      p.date, 
      p.username
  FROM forum_cat c
          LEFT JOIN (
              SELECT 
                  ft.id, 
                  ft.cat_id, 
                  ft.title, 
                  fp.date, 
                  u.username
              FROM forum_topic ft
                  JOIN forum_post fp ON fp.topic_id = ft.id
                  JOIN user u ON u.user_id = fp.author_id
              WHERE ft.cat_id = c.id
              ORDER BY fp.date DESC
              LIMIT 1
           ) p ON p.cat_id = c.id
  WHERE c.main_cat = ?
  ORDER BY c.list_no
  </code></pre>
  
  <p>Now the important thing I need here... <code>FOR EACH</code> category, I want to show the latest post and topic title in each category.
  However, this select statement is going INSIDE a foreach loop looping around the general categories which is found my main_cat.
  So there are 5 main categories with 3-8 subcategories.. this is the subcategory query. BUT FOR EACH subcategory, I need to grab the latest post.. However, it only runs this SELECT query for each main category so it's only select THE LATEST post between all subcategories combined... I want to get the latest post of EACH subcategory, but I rather not run this query for each subcategory... since I want the page load to be fast.
  BUT REMEMBER, some subcategories WILL NOT have a latest post since some of them may not even contain a topic yet! So hence the left join.</p>
  
  <p>Does anyone know how to go about doing this?</p>
  
  <p>AND BTW, there is an error it gives me <code>(WHERE ft.cat_id = c.id)</code> in the subquery because <code>c.id</code> is an unknown column. But I'm trying to reference it from the outer query so can someone help me on that issue as well?</p>
  
  <p>Thank you!</p>
  
  <p>All tables:</p>
  
  <pre class="lang-none prettyprint-override"><code>forum_cat (Subcategories)
  -----------------------------------------------
  ID, Title, Description, Icon, Main_cat, List_no
  
  forum_topic (Topics in each subcategory)
  --------------------------------------------
  ID, Author_id, Cat_id, Title, Sticky, Locked
  
  forum_post (Posts in each topic)
  --------------------------------------------
  ID, Topic_id, Author_id, Body, Date, Hidden'
  </code></pre>
  
  <p>The main categories are listed in a function. I didn't store them in the database since it was a waste of space since they never change. There are 7 main categories though.</p>