Re: FN-FORUM: Help/guidance with a bit of tricky php/MySQL

date posted 4th March 2008 13:23



> Many thanks to both of you, Graham and Joe.
>
> Question is, which is most efficient?
>

Joe's, almost certainly. I find subselects easier to think about,
though

Graham


> Option 1 (thanks Graham):
>
> SELECT productid, variant, sum(quantity) AS quantity_alias from Order_items
> WHERE orders_id IN ( select Orders_id from orders where status = 'Paid' )
> group by productid, variant
> order by quantity_alias desc;
>
>
> Option 2 (thanks Joe):
>
> SELECT oi.productid, oi.variant, sum(oi.quantity) AS quantity_alias
> from Order_items OI
> join Orders o on oi.order = o.orderid
> WHERE o.orderstatus = Paid
> group by oi.productid, oi.variant
> order by quantity_alias desc;
>
> Any ideas?
> Duncan
>
--
Graham Stark, Virtual Worlds, http://www.virtual-worlds.biz
Phone (+44) 01908 618239 Mobile (+44) 07952633185 Skype graham_k_stark