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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
