title: SQL Join question, return null if second table is empty
body: <p>I have 2 tables.</p>
  
  <p><strong>table 1 (projects): id, name, type</strong></p>
  
  <p><strong>table 2 (project_ratings): project_id, rating</strong></p>
  
  <p>Some projects don't have ratings.</p>
  
  <pre><code>SELECT `p`.`id`, `p`.`name`, AVG(pr.rating) FROM (`projects` p) JOIN `project_ratings` pr ON `p`.`id` = `pr`.`project_id` WHERE `p`.`type` = 'group' OR `p`.`type` = 'user';
  </code></pre>
  
  <p>I want to return all projects, and return NULL if there aren't any ratings. This query returns only the ones with ratings.</p>
  
  <p>I tried left join, right join, full join, still same thing.</p>
  
  <p>Using CodeIgniter Active Records:</p>
  
  <pre><code>$this-&gt;db-&gt;select("p.id, p.name, AVG(pr.rating)");
          $this-&gt;db-&gt;from('projects p');
          $this-&gt;db-&gt;join('project_ratings pr', 'p.id = pr.project_id');
  
          $this-&gt;db-&gt;where('p.type', 'group');
          $this-&gt;db-&gt;or_where('p.type', 'user');
  $res = $this-&gt;db-&gt;get();
  </code></pre>
  
  <p>what am I missing?</p>