title: How to join and get sum from three different tables in codeigniter?
body: <p>I have a case like this, I want to make a report from three different tables in CodeIgniter,  and then make a summary from other tables, for more detail check this out :</p>
  
  <p>I've table <code>tbl_product</code> :</p>
  
  <pre><code>+----------+----------+---------+
  | id_prdct | product  | remark  |
  +----------+----------+---------+
  |    1     | marriage | remark1 |
  |    2     | office   | remark2 |
  |    3     | school   | remark3 |
  +----------+----------+---------+
  </code></pre>
  
  <p>then I've tables <code>tbl_sell</code> like below :</p>
  
  <pre><code>+---------+----------+------------+------------+
  | id_sell | id_prdct | item_sell  | price_sell |
  +---------+----------+------------+------------+
  |    1    |    1     | dress      | USD 500    |
  |    2    |    1     | shoes      | USD 600    |
  |    3    |    1     | decoration | USD 1500   |
  |    4    |    2     | dress      | USD 300    |
  |    5    |    2     | shoes      | USD 500    |
  |    6    |    3     | shoes      | USD 900    |
  +---------+----------+------------+------------+
  </code></pre>
  
  <p>and <code>tbl_buy</code> like below :</p>
  
  <pre><code>+--------+----------+------------+------------+
  | id_buy | id_prdct | item_buy   | price_buy  |
  +--------+----------+------------+------------+
  |    1   |    1     | dress      | USD 250    |
  |    2   |    1     | shoes      | USD 300    |
  |    3   |    1     | decoration | USD 1000   |
  |    4   |    2     | dress      | USD 200    |
  |    5   |    2     | shoes      | USD 300    |
  |    6   |    3     | shoes      | USD 500    |
  +--------+----------+------------+------------+
  </code></pre>
  
  <p>In my web page, I want to make a result like below :</p>
  
  <pre><code>+----+----------+------------+----------+----------+
  | No | Product  | Sum Sell   | Sum Buy  | Profit   |
  +----+----------+------------+----------+----------+
  |  1 | marriage | USD 2600   | USD 1550 | USD 1050 |
  |  2 | office   | USD 800    | USD 500  | USD 300  |
  |  3 | school   | USD 900    | USD 500  | USD 400  |
  +----+----------+------------+----------+----------+
  </code></pre>
  
  <p>so I want to make a summary from <code>tbl_sell</code> and <code>tbl_buy</code>, and then show them on my web page then make a summary (profit) as my last table in above.
  is there any suggestion to solve my case?</p>
  
  <p>Thanks...</p>
  
  <p>==========================================================
  [UPDATE ANSWER]</p>
  
  <p>I already tried this but still got error, any suggestion for this code?</p>
  
  <pre><code>$d['data'] = $this-&gt;db-&gt;query("select sum(tbl_sell.price) AS total_sell, sum(tbl_buy.price) AS total_buy
              left join tbl_sell on tbl_sell.id_prdct=tbl_product.id_prdct
              left join tbl_buy on tbl_buy.id_prdct=tbl_product.id_prdct
              from tbl_product 
              WHERE tbl_sell.id_prdct=tbl_product.id_prdct AND tbl_buy.id_prdct=tbl_product.id_prdct ");
  </code></pre>