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
[email protected]
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/[email protected]
