List:General Discussion« Previous MessageNext Message »
From:John Nichel Date:July 8 2010 2:59pm
Subject:Problem with IF() inside of a select statement
View as plain text  
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 

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