Re: Problem with IF() inside of a select statement
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
RE: Problem with IF() inside of a select statement
> -Original Message- > From: Peter Brawley [mailto:peter.braw...@earthlink.net] > Sent: Thursday, July 08, 2010 12:27 PM > To: mysql@lists.mysql.com > Subject: Re: Problem with IF() inside of a select statement > > >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 > > - > Thank you for the reply. Unfortunately, not all of the rows I'm selecting in the main clause will have an entry in the other table, so if I do it that way, it won't return any data for the rows that don't have a match in the order_details table. > > 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. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with IF() inside of a select statement
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
