php - 如何从Codeigniter中的三个不同的表连接并获取和？

我有一个这样的案例，我想在CodeIgniter中从三个不同的表生成一个报告，然后从其他表生成一个摘要，有关更多详细信息，请查看：
我有桌子：

+----------+----------+---------+
| id_prdct | product  | remark  |
+----------+----------+---------+
|    1     | marriage | remark1 |
|    2     | office   | remark2 |
|    3     | school   | remark3 |
+----------+----------+---------+

我有如下表格：

+---------+----------+------------+------------+
| 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    |
+---------+----------+------------+------------+

并tbl_product如下：

+--------+----------+------------+------------+
| 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    |
+--------+----------+------------+------------+

在我的网页中，我想得到如下结果：

+----+----------+------------+----------+----------+
| 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  |
+----+----------+------------+----------+----------+

所以我想从tbl_sell和tbl_buy做一个总结，然后在我的网页上显示它们，然后做一个总结（利润）作为我在上面的最后一个表。
有什么解决我案子的建议吗？
谢谢。。。
==========================================================
[更新答案]
我已经试过了，但还是有错误，有什么建议吗？

$d['data'] = $this->db->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 ");

最佳答案

尝试此sql:

SELECT p.id_prdct,
       p.product,
       sell.total,
       buy.total
FROM   tbl_product p
       LEFT JOIN (SELECT id_prdct,
                         Sum(price_sell) total
                  FROM   tbl_sell
                  GROUP  BY id_prdct) sell
              ON p.id_prdct = sell.id_prdct
       LEFT JOIN (SELECT id_prdct,
                         Sum(price_buy) total
                  FROM   tbl_buy
                  GROUP  BY id_prdct) buy
              ON p.id_prdct = buy.id_prdct;

关于php - 如何从Codeigniter中的三个不同的表连接并获取和？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53939370/