join from three table - with date range / Forums / Community / EllisLab

Forum Logo

join from three table - with date range

Posted: 11 October 2012 10:04 AM

[ Ignore ]

Joined: 2012-10-11
3 posts

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 :

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 :

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

Signature

Regards
Vaibhav Jain

CroNiX

Posted: 11 October 2012 02:12 PM

[ Ignore ] [ # 1 ] [ Rating: 0 ]

Joined: 2009-02-19
3801 posts

do:


echo $this->db->last_query();

right after it runs and examine the SQL it produced.

Signature

paulkkarns

Posted: 12 October 2012 05:48 AM

[ Ignore ] [ # 2 ] [ Rating: 0 ]

Joined: 2012-10-12
1 posts

Thanks for reply, But i want more details. i am newbie here.

Signature

Hire Magento Developer

Vaibhav Jain

Posted: 13 October 2012 06:42 AM

[ Ignore ] [ # 3 ] [ Rating: 0 ]

Joined: 2012-10-11
3 posts

Thanks CroNiX for prompt reply,
after the examine the sql I found that there is prob with my SQL, but as I’m new in CI and SQL so don’t know how to solve this issue.
find the below sql for tbl_receiving_temp and tbl_sales_tamp

sql for tbl_receivings_temp

CREATE TEMPORARY TABLE tbl_receivings_items_temp (SELECT date(receiving_time) as receiving_date, tbl_receivings_items.receiving_id, comment,payment_type, employee_id, tbl_items.item_id, tbl_receivings.supplier_id, quantity_purchased, item_cost_price, item_unit_price, discount_percent, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) as subtotal, tbl_receivings_items.line as line, serialnumber, tbl_receivings_items.description as description, ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100),2) as total, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) - (item_cost_price*quantity_purchased) as profit FROM tbl_receivings_items INNER JOIN tbl_receivings ON tbl_receivings_items.receiving_id=tbl_receivings.receiving_id INNER JOIN tbl_items ON tbl_receivings_items.item_id=tbl_items.item_id GROUP BY receiving_id, item_id, line) 

sql for tbl_sales_temp

(SELECT date(receiving_time) as receiving_date, tbl_receivings_items.receiving_id, comment,payment_type, employee_id, 
        tbl_items.item_id, tbl_receivings.supplier_id, quantity_purchased, item_cost_price, item_unit_price,
        discount_percent, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) as subtotal,
        tbl_receivings_items.line as line, serialnumber, tbl_receivings_items.description as description,
        ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100),2) as total,
        (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) - (item_cost_price*quantity_purchased) as profit
        FROM tbl_receivings_items
        INNER JOIN tbl_receivings ON  tbl_receivings_items.receiving_id=tbl_receivings.receiving_id
        INNER JOIN tbl_items ON  tbl_receivings_items.item_id=tbl_items.item_id
        GROUP BY receiving_id, item_id, line) 

and this is mu sql which is not correct , so I need to resolve below SQL.

SELECT `name`, sum(quantity_purchased) as "Qty Sold", `tbl_items`.`quantity` as "Qty remain", sum(tbl_inventory.trans_items) as "reports_quantity_received"
FROM (`tbl_sales_items_temp`)
INNER JOIN `tbl_items` ON `tbl_sales_items_temp`.`item_id` = `tbl_items`.`item_id`
LEFT OUTER JOIN `tbl_inventory` ON `tbl_items`.`item_id` = `tbl_inventory`.`trans_items`
WHERE `trans_inventory` >= 0
AND `sale_date` BETWEEN "2011-10-13" and "2012-10-13"
AND `trans_date` BETWEEN "2011-10-13" and "2012-10-13"
GROUP BY `tbl_items`.`item_id`
ORDER BY `name` 

Signature

Regards
Vaibhav Jain