Posted by Hilarion on 11/17/05 13:19

> I have been working on this and have figured out how to get the "cost_price"
> figure for the products to be ordered. There are actually three tables I had
> to join to get the data...here is the SQL statement I came up with...
>
> $retrieve_cost_price = mysql_query(
> ' SELECT cost_price' .
> ' FROM cubecartstore_order_inv, cubecartstore_order_sum,
> cubecartstore_inventory' .
> ' WHERE cubecartstore_order_inv.cart_order_id =
> cubecartstore_order_sum.cart_order_id' .
> ' and cubecartstore_order_inv.product = cubecartstore_inventory.product' .
> ' and cubecartstore_order_inv.order_from_supplier = 1'
> );
>
> I'm sure there is a shorter way of doing the above but hey...I FIGURED IT
> OUT!
>
> Now I just have to figure out how to tie it all together...my brain is
> spinning at the moment so I'm going to take a break and come back to
> it...but any advice anyone could offer in the meantime would be greatly
> appreciated...


I do not see why you are using "cubecartstore_order_sum" table in your
SELECT. It is not a source of any data (as I understand the "cost_price"
comes from "cubecartstore_inventory") and it does not serve as join
table (a way to join "cubecartstore_order_inv" with "cubecartstore_inventory").

I removed that table from the SELECT, changed your way of joining tables
to use INNER JOIN clause and used it as a source for aggregation (and
also used table aliases to make references to columns shorter):

SELECT
ord.product,
ord.title,
SUM( ord.quantity ) AS sum_quantity,
inv.cost_price,
inv.cost_price * SUM( ord.quantity ) AS order_value
FROM
cubecartstore_order_inv AS ord INNER JOIN
cubecartstore_inventory AS inv ON ord.product = inv.product
WHERE
ord.order_from_supplier = 1
GROUP BY
ord.product,
ord.title,
inv.cost_price
ORDER BY
ord.product ASC,
ord.title ASC

The total sum of all "order_value" you can calculate in the PHP
script (you could do it in SQL but why bother).


Hilarion

[Back to original message]