title: Help with Select, Join across multiple tables with CodeIgniter - MYSQL
body: <p>I am trying to make a query in Codeigniter that will allow a user to retrieve a record they submitted in the past to my database.</p>
  
  <p>This is my schema:</p>
  
  <p><a href="https://i.imgur.com/Dju0G.png" rel="nofollow noreferrer">http://i.imgur.com/Dju0G.png</a></p>
  
  <p><img src="https://i.stack.imgur.com/RVIAw.png" alt="enter image description here"></p>
  
  <p>I am having trouble figuring out what I need to do for some of my tables</p>
  
  <p><strong>For example 1:M or M:M</strong></p>
  
  <ol>
  <li>1 <em>procedure</em> has 1 or many <em>procedure_event</em></li>
  <li>a <em>procedure_event</em> may or may not have use resources</li>
  <li>1 procedure has 1 or many staff</li>
  </ol>
  
  <p>I think I need to use Select, Joins for this and I have got some of it completed but for the Many to Many relationships and linked tables I am confused how to do them.</p>
  
  <p>This is what I have got so far in terms of code but it is not returning any rows so I think it is wrong:</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
          $criteria = array 
          (
              'procedure_id' =&gt; $record_id
          );
  
          //this selects the columns from procedure table for use in joins 
          $this-&gt;db-&gt;select('procedure.procedure_id, procedure.patient_id, procedure.department_id, procedure.name_id , procedure.dosage_id');
          //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', 'inner');
  
          //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', 'inner');
  
          //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', 'inner');
  
          //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', 'inner');
  
          //this selects the row in procedure table that matches the record number
          $this-&gt;db-&gt;where('procedure_id', $record_id);
  
          /*
          Code for other tables:
          I need help with
  
          procedure_event, staff, resources, hr
  
          */
  
          //this part I think is wrong
          $result = $this-&gt;db-&gt;get();
  
  
          //
          if ($result-&gt;num_rows &gt;0)
          {
              echo "There is Data!";
  
          }
  
          else
          {
              echo "No Data!";
  
          }
  </code></pre>
  
  <p>}</p>
  
  <p>I get back a message saying <strong>"No Records"</strong> however there is data in my tables etc:</p>
  
  <p>So my query so far must be wrong.</p>
  
  <p>Thanks for your time!</p>