>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
>
