join from three table - with date range

[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

[eluser]CroNiX[/eluser]
do:

Code:

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

right after it runs and examine the SQL it produced.

[eluser]Unknown[/eluser]
Thanks for reply, But i want more details. i am newbie here.

[eluser]Vaibhav Jain[/eluser]
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

Code:

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

Code:

(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 :down:, so I need to resolve below SQL.

Code:

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`