title: Subquery with LIMIT in MYSQL complex query
body: <p>The Mysql version i'm using don't let me use a LIMIT inside subquery so i can speed up the query. How to use join instead in the next query?</p>
  
  <pre><code>SELECT p . * , (
  
  SELECT AVG( review_rating ) AS rating_total
  FROM reviews r
  WHERE r.review_item_ref = p.pattern_ref
  AND r.review_state =1
  GROUP BY r.review_item_ref
  ) AS review_rating
  FROM patterns p
  WHERE pattern_active &gt;=0
  AND p.pattern_family =27
  AND p.pattern_ref
  IN (
  
  SELECT item_pattern_ref
  FROM items
  WHERE item_pattern_ref = p.pattern_ref LIMIT 1
  )
  GROUP BY p.pattern_id
  ORDER BY p.pattern_description ASC , LCASE( p.pattern_description ) ASC
  LIMIT 0 , 16
  </code></pre>