Making report from 4 tables details
I'm trying to get the total of amount sold of the items in the store & the total of cash receipts. I used certain date .. it works find but i face several wrong data.
i tried this query:
orders.id, orders_items.orderid, orders_items.id AS ITEM_ID, orders.userid, users.userid, users.fname, SUM((orders_items.price - orders_items.discount) * (orders_items.quantity + orders_items.quantity_store)) AS ITEM_PRICE, orders_items.`date` AS ITEM_DATE, orders_receipts.`date` AS RECEIPT_DATE, SUM(orders_receipts.amount) AS RECEIPT_AMOUNT, orders_receipts.orderid, orders_receipts.id AS REC_ID, orders_receipts.check_date
INNER JOIN orders_items ON orders.id = orders_items.orderid
INNER JOIN orders_receipts ON orders.id = orders_receipts.orderid
INNER JOIN users ON orders.userid = users.userid
orders_items.`date` BETWEEN '2010-03-01' AND '2010-03-31' OR orders_receipts.`date` BETWEEN '1267390800' AND '1268168400'
1- if there is no receipt paid the items sold will not show in the list!
2- if there are many items sold, the receipts gets multiplied by the number of items!
3- if there are many receipts, the items price gets multiplied by the number of receipts!
Note: tables digram attached.