title: MySQL left join with right table having order and limit
body: <p>i have a table of itineraries and a table of associated images with a foreign key.  i'd like to get a list of itineraries with its first image, so ordering itineraries_images by sort with a limit of 1</p>
  
  <pre><code>CREATE TABLE itineraries (
    id int(10) AUTO_INCREMENT,
    is_live tinyint(1),
    title varchar(255),
    body text,
    PRIMARY KEY (id) 
  )
  
  CREATE TABLE itineraries_images (
    id int(10) AUTO_INCREMENT,
    itineraries_id int(10),
    is_live tinyint(1),
    caption varchar(255),
    image_src varchar(255),
    sort smallint(5),
    PRIMARY KEY (id),
    KEY itineraries_id (itineraries_id)
  )
  </code></pre>
  
  <p>i'm doing a left join, but it doesn't sort the joined table</p>
  
  <pre><code>SELECT i.*, ii.image_src, ii.caption 
  FROM itineraries AS i 
  LEFT OUTER JOIN itineraries_images AS ii ON i.id=ii.itineraries_id AND ii.is_live=1 
  WHERE i.is_live=1 
  GROUP BY ii.itineraries_id  
  ORDER BY i.id, ii.sort
  </code></pre>
  
  <p>been looking at subqueries... but still can't get it working :(</p>
  
  <p>many thanks,</p>
  
  <p>rob.</p>