title: MySQL Join table row based on lowest cell value
body: <p>I have two tables in a MySQL database like this:</p>
  
  <p>PRODUCT:</p>
  
  <pre><code>product_id | product_name
  -----------+-------------
  1          |  shirt
  2          |  pants
  3          |  socks
  </code></pre>
  
  <p>PRODUCT_SUPPLIER: (id is primary key)</p>
  
  <pre><code>id  |  supplier_id  |  product_id  |  part_no  |  cost
  ----+---------------+--------------+-----------+--------
  1   |  1            |  1           | s1p1      | 5.00
  2   |  1            |  2           | s1p2      | 15.00
  3   |  1            |  3           | s1p3      | 25.00
  4   |  2            |  1           | s2p1      | 50.00
  5   |  2            |  2           | s2p2      | 10.00
  6   |  2            |  3           | s2p3      | 5.00
  </code></pre>
  
  <p>My goal is a query that joins the tables and outputs a single row for each product joined with all fields from the corresponding supplier row with the lowest cost like this:</p>
  
  <pre><code>product_id | product_name  | supplier_id   |  part_no   | cost
  -----------+---------------+---------------+------------+---------
  1          | shirt         | 1             |  s1p1      | 5.00
  2          | pants         | 2             |  s2p2      | 10.00
  3          | socks         | 2             |  s3p3      | 5.00
  </code></pre>
  
  <p>At present I do have the following query written which seems to work but I'd like to know from any of the more experienced SQL users if there is a cleaner, more efficient or otherwise better solution?  Or if there is anything essentially wrong with the code I have?</p>
  
  <pre><code>SELECT p.product_id, p.product_name, s. supplier_id, s.part_no, s.cost
  FROM product p
  LEFT JOIN product_supplier s ON
     (s.id = (SELECT s2.id 
              FROM product_supplier s2
              WHERE s2.product_id = p.product_id
              ORDER BY s2.cost LIMIT 1));
  </code></pre>