title: Which join for 1: Many relationship?
body: <p>Here is my Database schema and the tables I am trying to link, I tried using a <strong>LEFT</strong> join but now it is creating a new result for each matched row.</p>
  
  <p><a href="http://i.imgur.com/Ovsau.png" rel="nofollow">http://i.imgur.com/Ovsau.png</a></p>
  
  <p>I am currently building up my query and now trying to join these tables together <strong>(Procedure-> Procedure_event -> Resources -> Med_item)</strong></p>
  
  <p>In a scenario like this:</p>
  
  <blockquote>
    <p>e.g. Procedure X has 3 rows procedure_events</p>
  </blockquote>
  
  <p>I want the data returned to me in one record but with all the rows that make up that record.</p>
  
  <p>However it is returning 3 rows of results like so:</p>
  
  <ul>
  <li>Procedure X - procedure_event row 1</li>
  <li>Procedure X - procedure_event row 2</li>
  <li>Procedure X - procedure_event row 3</li>
  </ul>
  
  <p>So the query result should be <strong>Procedure X - procedureevent rows 1,2,3</strong></p>
  
  <p>This is my code in Codeigniter PHP framework using Active Record:</p>
  
  <pre><code>$this-&gt;db-&gt;join('procedure_event', 'procedure.procedure_id = procedure_event.procedure_id' , 'left');
  </code></pre>
  
  <p>Thanks</p>