[eluser]Vaibhav Jain[/eluser]
Hello everyone,
Well I also have a issue with join.
I have three tables :
1.)tbl_sales 2.) tbl_items 3.) tbl_inventory
Now what happens when I create join with sale and item tables then it works fine and when i include third column from inventory table then data comes wrongly. Don't know what I'm doing wrong, may be some join problem.
here is the right code :
Code:
public function getData(array $inputs)
{
$this->db->select('name, sum(quantity_purchased) as "Qty Sold", tbl_items.quantity');
$this->db->from('sales_items_temp');
$this->db->join('items', 'sales_items_temp.item_id = items.item_id');
$this->db->where('sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
$this->db->group_by('items.item_id');
$this->db->order_by('name');
return $this->db->get()->result_array();
}
:
Code:
public function getData(array $inputs)
{
$this->db->select('name, sum(tbl_inventory.trans_inventory) as "reports_quantity_received", sum(quantity_purchased) as "Qty Sold", tbl_items.quantity as "Qty remain"') ;
$this->db->from('sales_items_temp');
$this->db->join('items', 'sales_items_temp.item_id = items.item_id');
$this->db->join('inventory','items.item_id = inventory.trans_items', 'LEFT OUTER');
$this->db->where('trans_inventory >= 0');
$this->db->where('sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
$this->db->or_where('trans_date BETWEEN "'.$inputs['start_date']. '" and "'.$inputs['end_date'].'"');
$this->db->group_by('items.item_id');
$this->db->order_by('name');
return $this->db->get()->result_array();
}
but this is not correct code, don't know what I'm doing wrong.:down:
If anyone know how to solve this issue then please let me know as soon as possible.
It would be great if someone give me a prompt reply..:-)
Thanks in advance..
Regards,
Vaibhav Jain
