MySQL – Is It Possible to Have an IF Subquery?

MySQL

I'm attempting to run a subquery inside of an if statement used in the SELECT, but I'm getting an error. I've never needed to do something similar before so I've never tried this until now. Here is what I'm running:

SELECT t1.*, IF(t1.tin > 0, (SELECT name FROM special_packaging WHERE id = t1.tin), 0), t2.productname, t2.imagepath FROM cartitems AS t1 INNER JOIN products AS t2 ON t1.productid = t2.id WHERE t1.cartid = 8

I'm getting a syntax error. Is it possible to run a subquery inside of an IF statement?

Any advice on what I'm doing wrong is greatly appreciated.

EDIT:

This is the error message I am getting: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Best Answer

I would probably convert it to a more-standard JOIN and a CASE statement, personally:

SELECT
     t1.*
    ,CASE WHEN t1.tin > 0 THEN sp.name ELSE 0 END
    ,t2.productname
    ,t2.imagepath
FROM cartitems AS t1
JOIN products AS t2
  ON t1.productid = t2.id
LEFT JOIN special_packaging sp
       ON sp.id = t1.tin
WHERE t1.cartid = 8
Related Question