|
|
Posted by John MacLeod on 11/16/05 23:26
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...
Thanks,
John
"John MacLeod" <jm@roast.ca> wrote in message
news:dlfo3h0si0@enews4.newsguy.com...
>
> The only field that may differ is the "price" because someone may have
> used a coupon...but to be honest I don't need the price in there anyway so
> I have taken it out.
>
> What I could use here, however, is the "cost_price" (what we pay our
> supplier) but that is stored in a different table "store_inventory" and
> I'm not sure how to retrieve that information from that table (which why I
> haven't even tried to this point) and link it to products listed from
> "store_order_inv" where "order_from_supplier" = 1? Unlike the "price"
> value, I know that the "cost_price" value will always be the same. Also,
> if I am able to retrieve the "cost_price" it would have to be a totaled...
> "cost_price" x "sum_quantity". And finally it would be useful to have a
> total of all the values in the "cost_price" fields to give us a total cost
> of our order from our supplier. This is WAY too much for me to figure out
> on my own...but hopefully you (or someone else) can lead me step by
> step...
>
> ...by the way, doing this isn't 100% necessary for us...we can get along
> without it...the real reason I want to do all of this is to see if I can
> (with help, obviously) figure it out, simply because SQL intrigues me and
> I wish I had more time on my hands to learn more about it...I applaud the
> people who do have a good knowledge of SQL as it opens up so many
> possibilities that us part-time "hackers" only dream of...OK, enough butt
> kissing for now...
>
>
>>
>> SELECT product, title, price, SUM( quantity ) AS sum_quantity
>> FROM store_order_inv
>> WHERE order_from_supplier = 1
>> GROUP BY product, title, price
>> ORDER BY product ASC
>>
>
> I took out the "price" field from the above and have got this
> working...thank you once again...
>
> Sincerely,
> John
>
>
Navigation:
[Reply to this message]
|