On 7/8/2010 10:59 AM, John Nichel wrote:
> Hi,
>
> I'm hoping what I'm trying to do can be done, but I can't seem to find
> the right syntax. I have the following query:
>
> SELECT
> ... snipped ...
> if(
> (
> select
> d.date
> from
> orders d
> left join
> order_details e
> on
> d.orderid = e.orderid
> where
> e.productid =
> a.productid
> order by
> d.date desc
> limit 1
> ) > 0, d.date, 0
The results of your dependent subquery should not exist beyond the
evaluation portion of the IF() processing. Therefore, the date column
of your results are also missing.
To get this same effect you would need to double-execute the query
within the IF() as in
IF((...subquery...) > 0, (...subquery...) ,0)
Or, you can make certain your subquery returns either a value or zero as
part of its logic and avoid the IF in the outer query.
Or, you add this subquery to your main query as another JOIN.
SELECT
...
FROM products a
INNER JOIN (...subquery...) as d
This has the advantage of only needing to execute the subquery once per
row of the main query and it gives you the chance to rewrite the IF()
clause as simply
IF(d.date > 0 , d.date, 0) as last_sold.
Of course, creating a separate table of just the appropriate orders.date
values (even if it's a temporary table) would provide the results even
faster (especially if you index it).
Yours,
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
