title: Select latest row in LEFT JOIN PHP MySQL CodeIgniter
body: <p>I am trying to achieve the following:
  I have two tables. One of the tables is called <code>characters</code> and the other one is called <code>experience</code>. Right now I want to print a list of all <code>characters</code> and linking the latest row in <code>experience</code> to it. Added to that rows in <code>characters</code> without a row in <code>experience</code> should still be shown.</p>
  
  <p>Here an example of the tables and desired output.</p>
  
  <pre><code>characters
  id   |   name   |
  ----------------|
  1    | TestChar |
  2    | NewChar  |
  3    | OldChar  |
  
  experience
  id |  char_id  |  experience  |
  ------------------------------|
  1  |  1        | 683185858    |
  2  |  2        | 85712849     |
  3  |  1        | 687293919    |
  4  |  1        | 794812393    |
  
  output
  name      |   experience   |
  ---------------------------|
  TestChar  | 794812393      |
  NewChar   | 85712849       |
  OldChar   | NULL           |
  </code></pre>
  
  <p>So far, I made this query and it seems to work in MySQL</p>
  
  <pre><code>SELECT c.name, e1.experience
  FROM characters c
  LEFT JOIN experience e1 ON e1.char_id = c.id 
  LEFT JOIN experience e2 ON e1.char_id = e2.char_id AND e2.id &gt; e1.id
  WHERE e2.id IS NULL;
  </code></pre>
  
  <p>Then, I want to implement this in CodeIgniter but that's where it goes wrong.
  The following is what I have right now, it fills in the c.name but the e1.exp remains empty.</p>
  
  <pre><code>$this-&gt;db-&gt;select('c.name, e1.exp');
  $this-&gt;db-&gt;from('characters as c');
  $this-&gt;db-&gt;join('experience as e1', 'e1.char_id = c.id', 'left');
  $this-&gt;db-&gt;join('experience as e2', 'e1.char_id = e2.char_id AND e2.id &gt; e1.id', 'left');
  $this-&gt;db-&gt;where('e2.id', NULL);
  </code></pre>
  
  <p>Is this related to my MySQL query being wrong? Is my implementation in CodeIgniter incorrect? Both?
  I appreciate every bit of advice!</p>