title: MySQL Two Table Min Value and Group By
body: <p>I have two tables, products, and products_description</p>
  
  <p>Products:</p>
  
  <pre><code>Product_ID    Product_Price
   1             10    
   2             20    
   3             10    
   4             20   
   5             10    
   6             20
   7             10    
   8             10   
   9             10    
  10             10
  </code></pre>
  
  <p>Products_Description:</p>
  
  <pre><code>Product_ID    Product_Name
   1             Pants - Black    
   2             Pants - Black    
   3             Pants - White    
   4             Pants - White   
   5             Pants - Red    
   6             Pants - Red
   7             Hat    
   8             Socks   
   9             Scarf    
  10             Bird
  </code></pre>
  
  <p>Basically I need to implement code with respect to the current code that's already present in the shopping cart.</p>
  
  <p>The implementation needs to GROUP BY products_name - but provide the cheapest one.</p>
  
  <p>So far I've got the following:</p>
  
  <pre><code>SELECT  p.products_id, p.products_price, pd.products_name
  
  FROM products p
  </code></pre>
  
  <p>// bof custom sub-query</p>
  
  <pre><code>join
  (
  SELECT pp.products_id, pp.products_name, min(pr.products_price) as min_price
  from products_description pp
  inner JOIN products pr 
  ON pp.products_id = pr.products_id
  group by pp.products_name
  )
  AS subq on p.products_id = subq.products_id and p.products_price = subq.min_price
  </code></pre>
  
  <p>// eof custom sub-query</p>
  
  <pre><code>LEFT JOIN products_description pd on p.products_id = pd.products_id
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>Product_ID    Product_Name    Product_Price
   1             Pants - Black   10 
   3             Pants - White   10 
   5             Pants - Red     10
   7             Hat             10   
   8             Socks           10
   9             Scarf           10  
  10             Bird            10
  </code></pre>
  
  <p>But what's happening is the pants is getting removed. And only products 7 - 10 are remaining behind....</p>
  
  <p>Any ideas anyone please?</p>