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