title: Selecting minimum within JOIN
body: <p>I have two tables (listed only fields important for the question):</p>
  
  <p>t_groups</p>
  
  <ul>
  <li>INT groupId PRIMARY</li>
  <li>VARCHAR(255) grname</li>
  </ul>
  
  <p>t_goods</p>
  
  <ul>
  <li>INT goodId PRIMARY</li>
  <li>INT groupId</li>
  <li>INT price</li>
  <li>VARCHAR(255) name</li>
  </ul>
  
  <p>Now I need a query, which selects group names and name of the cheapest good in each group. Tried doing it this way:</p>
  
  <pre><code>SELECT gr.groupId, grname, g.name
  FROM t_groups AS gr
  LEFT JOIN (SELECT * FROM t_goods ORDER BY PRICE ASC LIMIT 1) AS g
  ON g.groupId = gr.groupId
  </code></pre>
  
  <p>but it doesn't work — returns NULLs in g.name field. It could be easily explained:</p>
  
  <p>SELECT within JOIN statement selects cheapest good first, and then tries to "filter it" by groupId. Obviously, it'll only work for the group cheapest good belongs to.</p>
  
  <p>How do I solve the task?</p>