Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share|improve this question
1  
It should work (even if it isn't necessarily the most optimum approach)... please post the actual error you get in response to that query. –  Michael - sqlbot Dec 31 '13 at 23:59
    
I added the error in my question above. –  Ryan Jan 2 at 14:09
    
Curious. You might try that query again; I see no syntax error in it. The ...the right syntax to use near '' at line... error message means the parser found whatever is shown in the quotes, at a place where that thing doesn't belong; empty string '' means the parser got to the end of the query while still in the middle of something and expecting more; example: SELECT * FROM; will generate the same error, because it expects something, anything, other than an empty string, after the FROM. (The semicolon would have already been removed). –  Michael - sqlbot Jan 2 at 16:21
    
I checked it again this morning and got the same result and I too am stumped. I've re-read the query and re-typed it thinking maybe I just typed it too quickly and overlooked a typo, but this exact query gives me the same error each time. Perhaps I should remove the * and try doing things the quicker and probably more accurate way. My boss has me in a hurry so I attempted to take the lazy route. –  Ryan Jan 2 at 16:54
add comment

1 Answer

up vote 3 down vote accepted

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
share|improve this answer
    
I just did this and it worked greatly. I really appreciate your help on this. –  Ryan Jan 2 at 14:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.