0

I have a MySQL statement that is erroring when it should perform smoothly. I have a WHERE clause with an AND statement where both elements of the AND statement have their own OR statements. I've confirmed that both OR statements work individually, but when I try combining both the second OR statement seems to break every time.

I've checked each piece of the WHERE clause individually to make sure nothing was written improperly or doesn't work. Everything checks out.

This is the statement that errors. It breaks on the second part of the AND statement. It does however work fine if event.Major = null. But when there is an actual value, it breaks.

WHERE (${event.Company} IS NULL OR (o.company_id = (SELECT id FROM launchpad.company WHERE name = '${event.Company}')))
    AND 
    (${event.Major} IS NULL OR (o.major_id = (SELECT id FROM launchpad.major WHERE name = '${event.Major}')))

This is the error I get when running the above clause: 

"Error: ER_PARSE_ERROR: 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 'Science IS NULL OR (o.major_id = (SELECT id FROM launchpad.major WHERE name = 'C' at line 13"

I'm expecting it to return all values if both Major and Company = null. Or return filtered values based on any combination of Major and Company.

Jet.B.Pope
  • 642
  • 1
  • 5
  • 25
  • 3
    Learn to use parameters and never have this problem again. – Gordon Linoff Oct 31 '19 at 02:18
  • @GordonLinoff is right, but in the meantime you're missing quotes around `${event.Major}` and `${event.Company}` in the `... IS NULL` parts of your query – Nick Oct 31 '19 at 02:23
  • That's what I originally had, but when the value is null it just ignores it when there are quotes around it. – Jet.B.Pope Oct 31 '19 at 02:26
  • 1
    If the value is null, you need to substitute the word `NULL` into the SQL, without quotes around it. Using parameters will solve this automatically. – Barmar Oct 31 '19 at 02:29

0 Answers0