title: Limit results from joined table to one row
body: <p>Here is a simplified table structure:</p>
  
  <pre><code>TABLE products (
   product_id INT (primary key, auto_increment),
   category_id INT,
   product_title VARCHAR,
   etc
  );
  
  TABLE product_photos (
   product_photo_id (primary key, auto_increment),
   product_id INT,
   photo_href VARCHAR,
   photo_order INT
  );
  </code></pre>
  
  <p>A product can have multiple photos, the first product photo for each product (based on the photo_order) is the default photo.</p>
  
  <p>Now, I only need all of the photos on the product details page, but on pages where I am listing multiple products, for example a product directory page, I only want to display the default photo.</p>
  
  <p>So what I am trying to do, is query a list of products including the default photo for each product.</p>
  
  <p>This obviously doesn't work, it will return all photos with the product info duplicated for each photo:</p>
  
  <pre><code>SELECT p.*, ph.*
  FROM products AS p
  LEFT JOIN product_photos AS ph
  ON p.product_id=ph.product_id
  ORDER BY p.product_title ASC
  </code></pre>
  
  <p>I need to figure out how to do something like this, but I don't know the syntax (or if it is possible)</p>
  
  <pre><code>SELECT p.*, ph.*
  FROM products AS p
  LEFT JOIN product_photos AS ph
      ON p.product_id=ph.product_id  **ORDER BY ph.photo_order ASC LIMIT 1**
  ORDER BY p.product_title ASC
  </code></pre>
  
  <p><strong>Edit: I figured out a solution with help from the answers below, thanks all!</strong></p>
  
  <pre><code>SELECT p.*, ph.*
  FROM products AS p
  LEFT JOIN product_photos AS ph 
      ON p.product_id=ph.product_id
      AND ph.photo_order =
      (
          SELECT MIN(z.photo_order)
          FROM product_photos AS z
          WHERE z.product_id=p.product_id
      )
  GROUP BY p.product_id
  ORDER BY p.product_title ASC
  </code></pre>