1

I run the following code in the mariadb terminal and it works fine. As soon as i put it into a .sql file and run it, I get the error shown below. The database is set up correctly, with the schema working fine. I'm trying to get an output like this like this:

 +----------+
 | ALCOHOL_FREE_COMPLIANT |
 +----------+
 |        0 |
 +----------+


SELECT NOT EXISTS (
    select market_postcode, truck_id, alcohol from (
          SELECT * FROM buybeverage JOIN beverage
    )AS beverageOrders
LEFT JOIN ChippOrder ON (chipporder.order_id = beverageOrders.order_id)

WHERE alcohol = 1 AND market_postcode = "E16AA"

) AS ALCOHOL_FREE_COMPLIANT;

ERROR 1064 (42000) at line 43: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT NOT EXISTS ( select market_postcode, truck_id, alcohol from ( ' at line 20

user2179936
  • 455
  • 2
  • 7
  • 16
  • 1
    That is indeed invalid SQL. A `select` should be followed by a list of expressions, not by `not exists`, which belongs in a `where` clause. – trincot Mar 19 '17 at 21:19
  • 1
    WHERE NOT EXISTS may be better. see here: http://stackoverflow.com/questions/1001144/mysql-select-x-from-a-where-not-in-select-x-from-b-unexpected-result – Peter Mar 19 '17 at 21:21
  • What would be the correct way to do this? – user2179936 Mar 19 '17 at 22:13

1 Answers1

2

...right syntax to use near 'SELECT NOT EXISTS...

Syntax errors report the text following the mistake. In this case, the SQL parser was expecting to see something other than SELECT.

I suspect you didn't put a semicolon (;) to terminate the SQL query before the one you show in your question above. For example:

SELECT blah blah FROM MyTable ORDER BY date

SELECT NOT EXISTS( ...

See how there's no semicolon after ORDER BY date? So the second SELECT is still being parsed as if it's part of the first query. And of course you can't put another SELECT following a complete query.

That's all to explain how to read the syntax error, because you will encounter this problem again in the future. We all do.

This has nothing to do with using NOT EXISTS. The comment from @trincot is not correct. You have written a valid SQL expression. NOT EXISTS () is just a boolean expression, and you can use it in the select-list following SELECT. It's not a common pattern, though.

How to solve this query better? Here's how I'd write it:

SELECT COUNT(*) = 0 AS ALCOHOL_FREE_COMPLIANT 
FROM buybeverage JOIN beverage 
  ON (...join condition, which you are missing...)
WHERE alcohol = 1 AND market_postcode = 'E16AA'

No need for subqueries.

No need to select any columns, since all you want to know is if there are zero or more than zero rows matching the conditions.

You need a join condition between buybeverage and beverage, or else you will generate a Cartesian product.

No need to join to ChippOrder as far as I can tell. But you haven't shown enough information for us to know which column belongs to which table.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828