From: Peter Brawley
Date: July 8 2010 4:27pm
Subject: Re: Problem with IF() inside of a select statement
List-Archive: http://lists.mysql.com/mysql/222118
Message-Id: <4C35FC70.9000607@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit



>Is there a way I
>can do a IF((SELECT.....), expr2, expr3) and have expr2 populate with
>whatever is returned from the select statement?

Yes, select if( (select count(*) from mytable ) > 100, 1, 0) works fine. 
The alias inside your last If(...), though, is not visible outside its 
parentheses; why not move that join logic to the query's main clause?

PB

-----


On 7/8/2010 9:59 AM, John Nichel wrote to:
> 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
> 			a.productid,
> 			a.productcode,
> 			a.product,
> 			if(
> 				a.local_stock = 'y' || a.is_commercial =
> 'n' || freight_class = '', 'y', 'n'
> 			) as local_stock,
> 			if(
> 				(
> 					SELECT
> 						count(b.productid)
> 					FROM
> 						pricing b
> 					WHERE
> 					a.productid = b.productid
> 				)>  1, 'y', 'n'
> 			) as price_breaks,
> 			if(
> 				a.productid in (select c.productid from
> variants c), 'y', 'n'
> 			) as is_variant,
> 			if(
> 				a.forsale = 'N', 'y', 'n'
> 			) as disabled,
> 			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
> 			) as last_sold
> 		FROM
> 			products a
>
> The query is erroring out on 'd.date' in expression two of the if
> statement, "#1109 - Unknown table 'd' in field list".  Is there a way I
> can do a IF((SELECT.....), expr2, expr3) and have expr2 populate with
> whatever is returned from the select statement?  Thank you.
>
> --
> John C. Nichel IV
> System Administrator
> KegWorks
> http://www.kegworks.com
> 716.362.9212 x16
> john@stripped
>
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.439 / Virus Database: 271.1.1/2989 - Release Date: 07/08/10 06:36:00
>