List:General Discussion« Previous MessageNext Message »
From:John Nichel Date:July 8 2010 4:46pm
Subject:RE: Problem with IF() inside of a select statement
View as plain text  
> -----Original Message-----
> From: Peter Brawley [mailto:peter.brawley@stripped]
> Sent: Thursday, July 08, 2010 12:27 PM
> To: mysql@stripped
> 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.
> >

Thread
Problem with IF() inside of a select statementJohn Nichel8 Jul
  • Re: Problem with IF() inside of a select statementPeter Brawley8 Jul
  • RE: Problem with IF() inside of a select statementJohn Nichel8 Jul
  • Re: Problem with IF() inside of a select statementMySQL)12 Jul