title: mysql - limit left join
body: <p>left say for instance that I have the following query and want to limit the <code>left join</code> on a table, how do I achieve that?</p>
  
  <pre><code>SELECT * FROM Base
  LEFT JOIN Derived1 ON Base.id = Derived1.id
  LEFT JOIN Derived2 ON Base.id = Derived2.id
  LEFT JOIN Derived3 ON (Base.id = Derived3.id WHERE Derived3.name LIKE 'test%' LIMIT 5) &lt;-----
  WHERE Base.id = 'someid';
  </code></pre>
  
  <hr>
  
  <p>I though I could write it like this:</p>
  
  <pre><code>SELECT * FROM Base
  LEFT JOIN Derived1 ON Base.id = Derived1.id
  LEFT JOIN Derived2 ON Base.id = Derived2.id
  LEFT JOIN Derived3 ON Base.id = (
      SELECT d3.id FROM Derived3 d3
      WHERE d3.name LIKE 'test%' AND Base.id = Derived3.id
      LIMIT 5
  )
  WHERE Base.id = 'someid';
  </code></pre>
  
  <p>but this gives me the incorrect result</p>
  
  <p>I want all data which is inside <code>Derived3</code> but only 5 items if Derived.name equals to <code>test 1</code>, <code>test 2</code>, <code>test 3</code> etc.
  There might be 100 000 000 tests inside that tables and a couple of thousand other stuff which doesn't start with <code>'test'</code>.</p>
  
  <p>So basically, give me anything inside <code>Derived3</code> and max 5 rows that start with <code>'test'</code>. </p>
  
  <p>How do I solve this?</p>