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