title: Joining 4 tables with Mysql and Codeigniter
body: <p>I have 4 tables, that's all in a relationship, yes they love each other :p</p>
  
  <ul>
  <li>Products</li>
  <li>Product Categories</li>
  <li>Product Category Relationships</li>
  <li>Product Images</li>
  </ul>
  
  <p>I want to join these 4 tables in one query, here is my Codeigniter code to do this;</p>
  
  <pre><code>function list_products($limit = 10, $offset = 0, $category)
  {
      $this-&gt;db-&gt;select('p.url, p.name, p.title, i.image');
      $this-&gt;db-&gt;from('products p');
      $this-&gt;db-&gt;join('product_categories c','c.id = r.category_id',
                      'left');
      $this-&gt;db-&gt;join('product_category_relations r',
                      'r.category_id = c.id','left');
      $this-&gt;db-&gt;join('product_images i',
                      'p.id = i.product_id AND i.default = 1','left');
      $this-&gt;db-&gt;where('c.url',$this-&gt;category_url.$category);
      $this-&gt;db-&gt;limit($limit,$offset);
      return $this-&gt;db-&gt;get()-&gt;result();
  }
  </code></pre>
  
  <p>When i execute this function / query, the result is an error about defining "r".</p>
  
  <blockquote>
    <p>Unknown column 'cms_r.category_id' in 'on clause'</p>
  </blockquote>
  
  <p>The query:</p>
  
  <blockquote>
    <p>SELECT <code>p</code>.<code>url</code>, <code>p</code>.<code>name</code>, <code>p</code>.<code>title</code>, <code>i</code>.<code>image</code> FROM
    (<code>cms_products</code> p) LEFT JOIN <code>cms_product_categories</code> c ON <code>c</code>.<code>id</code> =
    <code>cms_r</code>.<code>category_id</code> LEFT JOIN <code>cms_product_category_relations</code> r ON
    <code>r</code>.<code>category_id</code> = <code>c</code>.<code>id</code> LEFT JOIN <code>cms_product_images</code> i ON
    <code>p</code>.<code>id</code> = <code>i</code>.<code>product_id</code> AND i.default = 1 WHERE <code>c</code>.<code>url</code> =
    'kategori/yilbasi' LIMIT 12</p>
  </blockquote>
  
  <p>Can you help me?</p>