Austin Hunter Austin Hunter - 1 month ago 11
MySQL Question

CASE WHEN mySQL keeps getting error

I am trying to use the CASE statement, but I keep getting a syntax error. I have followed the docs but I cannot seem to get rid of this syntax error:
query:

SELECT students.firstname, students.lastname, students.tnumber, grades.tnumber
FROM students, grades
CASE WHEN grades.tnumber=students.tnumber AND grades.courseid='CSC2110' AND (grades.Grade='A' OR grades.Grade='B' OR grades.Grade='C')THEN 'YES' ELSE 'NO' END


error:

error executing query. MySQL: 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 'CASE WHEN grades.tnumber=students.tnumber AND grades.courseid='CSC2110' A' at line 1


Ignore the tables and select statements. I am more concerned are checking the syntax of my CASE statement and why it is throwing an error.

Answer

It isn't completely clear what you want the CASE expression to do, but it appears that it should be in the SELECT list. I also moved the join condition, which strangely appeared in the CASE expression, to the ON clause.

SELECT students.firstname,
       students.lastname,
       students.tnumber,
       grades.tnumber,
       CASE WHEN grades.courseid = 'CSC2110' AND
                 (grades.Grade='A' OR grades.Grade='B' OR grades.Grade='C')
            THEN 'YES' ELSE 'NO' END AS someColumn
FROM students
INNER JOIN grades
    ON grades.tnumber = students.tnumber