# 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:

``````

SELECT DISTINCT
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
FROM
orders
INNER JOIN orders_items ON orders.id = orders_items.orderid
INNER JOIN orders_receipts ON orders.id = orders_receipts.orderid
WHERE
orders_items.`date` BETWEEN '2010-03-01' AND '2010-03-31' OR orders_receipts.`date` BETWEEN '1267390800' AND '1268168400'
GROUP BY
orders.id
ORDER BY
orders.id DESC

``````

WRONG DATA:
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.

I was able to solve Problem No (1 & 2) by adding “Distinct” after the sum.

``````

SELECT DISTINCT
orders.id, orders_items.orderid, orders_items.id AS ITEM_ID, orders.userid, users.userid, users.fname, SUM(Distinct((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(Distinct orders_receipts.amount) AS RECEIPT_AMOUNT, orders_receipts.orderid, orders_receipts.id AS REC_ID, orders_receipts.check_date
FROM
orders
INNER JOIN orders_items ON orders.id = orders_items.orderid
INNER JOIN orders_receipts ON orders.id = orders_receipts.orderid
WHERE
orders_items.`date` BETWEEN '2010-03-01' AND '2010-03-31' OR orders_receipts.`date` BETWEEN '1267390800' AND '1268168400'
GROUP BY
orders.id
ORDER BY
orders.id DESC

``````

Now i want to show the items sold in the period selected even if the order not paid (no receipts add to the order)

that’s a suboptimal solution

the “multiples” problem that you had was due entirely to the fact that you were joining two one-to-many relationships in the query

so if there were 13 of one relationship and 12 of the other, the join would generate 156 rows!!!

then if you use SUM(DISTINCT …) you do get the right result, however, this merely maskes the underlying inefficiency

does that make sense?

try this –

``````SELECT orders.id
, users.userid
, users.fname
, oi.ITEM_PRICE
, r.RECEIPT_AMOUNT
FROM orders
INNER
ON users.userid = orders.userid
INNER
JOIN ( SELECT orderid
, SUM((price - discount) *
(quantity + quantity_store))
AS ITEM_PRICE
FROM orders_items
WHERE `date` BETWEEN '2010-03-01'
AND '2010-03-31'
GROUP
BY orderid ) AS oi
ON oi.orderid = orders.id
LEFT OUTER
JOIN ( SELECT orderid
, SUM(amount) AS RECEIPT_AMOUNT
FROM orders_receipts
WHERE `date` BETWEEN '1267390800'
AND '1268168400'
GROUP
BY orderid ) AS r
ON r.orderid = orders.id

``````

thank you r937 for your help.
it is showing now only the items which does not have receipts.
which means i got what i want but i lost another one.

let me try it again … one minute i will get back to you

it is working fine & the query runs fast but i noticed that when i go to an old order & add a new receipt the new receipt not showing in the query.

that’s due to the date restriction on the order_items table, which can be traced right back to your original query in post #1

Yes correct. but what i need is getting the order_items in the given date (done).
OR orders_receipts in the given date.

In that case i get only order_items AND orders_receipts in the given date.

EXAMPLE:
Case (1): I have a new items sold today & i got the payment (orders_receipts) today as well. When i search for the orders & the receipts for today date using the query given i get the sold items from table (order_items) & receipts payment from table (orders_receipts). WORKS GREAT

Case (2): I have an old items sold from 01-01-2009 in the table (order_items). but i got the payment (orders_receipts) today only. When i search in the orders & the receipts i got for today date using the query given i get NOTHING While i have to see the new receipt payment form table (orders_receipts). NOT Working for me yet

I should get the result in the two cases. while what i got it working now is only case 1.
I hope you got my point.

ah yes, i see

okay, try this –

``````SELECT orders.id
, users.userid
, users.fname
, oi.ITEM_PRICE
, [COLOR="Blue"]oi.latest_order_time_date[/COLOR]
, r.RECEIPT_AMOUNT
, [COLOR="blue"]r.latest_order_receipt_date[/COLOR]
FROM orders
INNER
ON users.userid = orders.userid
INNER
JOIN ( SELECT orderid
, [COLOR="blue"]MAX(`date`) AS latest_order_time_date[/COLOR]
, SUM((price - discount) *
(quantity + quantity_store))
AS ITEM_PRICE
FROM orders_items
GROUP
BY orderid ) AS oi
ON oi.orderid = orders.id
LEFT OUTER
JOIN ( SELECT orderid
, [COLOR="blue"]MAX(`date`) AS latest_order_receipt_date[/COLOR]
, SUM(amount) AS RECEIPT_AMOUNT
FROM orders_receipts
GROUP
BY orderid ) AS r
ON r.orderid = orders.id
[COLOR="blue"]WHERE oi.latest_order_time_date
BETWEEN '2010-03-01'
AND '2010-03-31'
OR r.latest_order_receipt_date
BETWEEN '1267390800'
AND '1268168400' [/COLOR]
``````

Great r937!
That’s what i need. but there are two things i noticed after i applied the new query.
2- i get the wrong figure of “amount” in the table “orders_receipts” & the figure of “SUM((price - discount) * (quantity + quantity_store))” in the table “orders_items”.

How?!

A- I get the total amount of orders_receipts of the order while i need only the total amount of orders_receipts in the specified date.

Case: I have an order with payment from long time & payment for today. when i search for today payment i get the both payments (Old & today).

B- I get the total amount of orders_items in the order while i need only the total amount of orders_items in the specified date.

Case: I have an order with items sold from long time & the order updated today with new items. when i search for today items sold i get the both items sold (Old & today).

sorry, i can’t fix those problems for you

based on what i’ve shown you so far (couple of different versions of where to have the date criteria) you should be able to adjust the query yourself

good luck

okay thank you r937, you have done so much help