List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:July 12 2010 4:16am
Subject:Re: Problem with IF() inside of a select statement
View as plain text  
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
Thread
Problem with IF() inside of a select statementJohn Nichel8 Jul
  • Re: Problem with IF() inside of a select statementPeter Brawley8 Jul
  • RE: Problem with IF() inside of a select statementJohn Nichel8 Jul
  • Re: Problem with IF() inside of a select statementMySQL)12 Jul