join from three table - with date range

[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`