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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
