|
|
Posted by John MacLeod on 11/18/05 19:02
Hilarion,
Thank you, once again, for your help...I was going to respond yesterday but
never had enough time...
(more below...)
"Hilarion" <hilarion@SPAM.op.SMIECI.pl> wrote in message
news:dlhout$c38$1@news.onet.pl...
>> 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").
>
....as I said, my head was spinning from thinking so much when I finally got
a number for "cost_price" to work...I was just thrilled to get the right
numbers. I must have been thinking for some reason that I needed the
cart_order_id to link everything together...I guess I forgot about the
order_from_supplier=1 as the qualifier...
> 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
>
Thanks for this, it works and as I read through it I actually "pretty much"
understand what's happening! I'll have to read a little more about inner
joins and outer joins, etc. before I understand it completely but I'm
further ahead than I was last week...
> The total sum of all "order_value" you can calculate in the PHP
> script (you could do it in SQL but why bother).
>
After some searching I finally figured out how to do this as well...it took
a while because I didn't define the variable...
Thanks again for your help...it's working the way I had hoped it would. But
of course I'm already thinking of things to add to it, like the selling
price totals and our net profit...but that will have to be another time as
I'm just too busy at the moment...
Sincerely,
John MacLeod
Navigation:
[Reply to this message]
|