I have a table name products with all product details and another whs_products with quantity details of the products for each warehouse.

i want select id, code and name from products table and sum of quantity where products.id = whs_products.product_id

I am trying this

$this->db->select("id, code, name");
$this->db->from("products");
$this->db->join('whs_products', 'products.id = whs_products.product_id');
$this->db->select("quantity");

I getting the list products that exists in whs_products not the sum. Some products are listed twice as they have 2 entries in whs_products.

I want list all the products once only where no quantity I want put 0 in quantity and where its is more than 1 in whs_products I want display sum of all the quantity

Help will be much appreciated!

Table Structure

Products
id, code, name, unit, price

whs_products
id, product_id, warehouse_id, quantity

I have whs table too for warehouse id, name, address

I tried this Sir,

$this->db->select("products.id as productid, products.code, products.name, products.unit, products.cost, products.price,   sum(whs_products.quantity) as 'totalQuantity'")
->from('products')
->join('whs_products', 'whs_products.product_id=products.id', 'left')
->group_by("products.id");
$this->db->get();

Every thing is fine. But the total number of products are calculated wrongly. I think system add 1 to total products, each time gets quantity from whs_products. For some products quantity is 2 or 3 time depending on each warehouse.

Any solutions for this. I am very thankful for your support.