join from three table - with date range

#1
[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();      
    }


and this is my code :
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


Messages In This Thread
join from three table - with date range - by El Forum - 10-11-2012, 07:04 AM
join from three table - with date range - by El Forum - 10-11-2012, 11:12 AM
join from three table - with date range - by El Forum - 10-12-2012, 02:48 AM
join from three table - with date range - by El Forum - 10-13-2012, 03:42 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.