title: My query is duplicating results? MySQL / PHP/ Codeigniter
body: <p>To make this a simple as possible to understand I am going to give you all the details I can so I can figure out this problem.</p>
  
  <hr>
  
  <p>Okay so my problem is that my query that fetches me <strong>ONE</strong> record from my database is returning what appears to be duplicate values of the same record in multiple arrays. When I check my query result all my tables are duplicated and placed into arrays - one array for each duplicated record (or row).</p>
  
  <p>The cause of this I think is my 1 --> Many relationship table (Procedure --> Procedure_event)</p>
  
  <p>Here is my shema (you will see procedure and procedure_event) are 1: M relationship.</p>
  
  <p><a href="http://i.imgur.com/Dju0G.png" rel="nofollow">http://i.imgur.com/Dju0G.png</a></p>
  
  <hr>
  
  <p><a href="http://pastebin.com/pc53Pmgf" rel="nofollow">Here</a> is my query result as you can see my tables <strong>(apart from procedure event)</strong> are repeated with the same data in each array. The quantity of arrays  returned is dependant on how many <strong>procedure_event</strong> rows there are linked to <strong>procedure</strong>.</p>
  
  <p>In this list there are three! --> <a href="http://pastebin.com/pc53Pmgf" rel="nofollow">http://pastebin.com/pc53Pmgf</a></p>
  
  <p>To illustrate to you what is happening here is a diagram:</p>
  
  <p>Query Result:</p>
  
  <blockquote>
    <p>procedure row x ---> procedure_event row x </p>
    
    <p>procedure row x ---> procedure_event row x+1</p>
    
    <p>procedure row x ---> procedure_event row x+1</p>
  </blockquote>
  
  <p>What I want to returned is this (no duplication of other tables):</p>
  
  <blockquote>
    <p>procedure row x ---> procedure_event x</p>
  
  <pre><code>           ---&gt; procedure_event x+1
  
              ---&gt; procedure_event x+1
  </code></pre>
  </blockquote>
  
  <hr>
  
  <p><strong>Here is my query in Codeigniter Active Record</strong></p>
  
  <pre><code>public function view_record($record_id)
          {
              //The criteria used for WHERE is a variable with array of procedure_id = record_id that is passed to function
              //e.g. Record id = 2419
              $criteria = array 
              (
                  'procedure_id' =&gt; $record_id
              );
  
              //this selects the columns from procedure table for use in joins 
              $this-&gt;db-&gt;select('*');
              //this is the table I want to use the columns from
              $this-&gt;db-&gt;from ('procedure');
  
              //this joins the patient row that matches its ID to the patient ID in the procedure table
              $this-&gt;db-&gt;join('patient', 'patient.patient_id = procedure.patient_id', 'left');
  
              //this joins the department row that matches its ID to the patient ID in the procedure table
              $this-&gt;db-&gt;join('department', 'department.department_id = procedure.department_id', 'left');
  
              //this joins the procedure_name row that matches its ID to the patient ID in the procedure table
              $this-&gt;db-&gt;join('procedure_name', 'procedure_name.procedure_name_id = procedure.name_id', 'left');
  
              //this joins the dosage row that matches its ID to the patient ID in the procedure table]
              $this-&gt;db-&gt;join('dosage', 'dosage.dosage_id = procedure.dosage_id', 'left');
  
              //-----&gt;This is what is causing the problem -- try a different JOIN?
              $this-&gt;db-&gt;join('procedure_event', 'procedure.procedure_id = procedure_event.procedure_id' , 'left');
  
              //this selects the row in procedure table that matches the record number
              $this-&gt;db-&gt;where('procedure.procedure_id', $record_id);
  
              //gets all the data from the query
              $result = $this-&gt;db-&gt;get();
  
  
              //
              if ($result-&gt;num_rows &gt;0)
              {
                  return $result-&gt;result();
              }
  
              else
              {
                  return FALSE;
  
              }
  
  
          }
  </code></pre>
  
  <p>I hope this is clear to everyone. I hope there is a way to solve this otherwise I am going to be stuck as I do not know how to deal with all this repeated data as it is messing up my foreach loops.</p>
  
  <p>In the words of Princess Leiah </p>
  
  <blockquote>
    <p>"Help me Obi'wan, your my only hope!"</p>
  </blockquote>
  
  <p>Thanks!</p>