CODEWITHSUNDEEP
mysqlsqldatabasemysql-error-1064
haskellnoob
haskellnoob

Reputation: 211

SQL Error in Syntax near except

I'm trying to select persons who have an account at all branches of the city. (With a SQL query)

SELECT A.customId 
FROM accountholder as A 
WHERE NOT EXISTS ( 
                   (SELECT name 
                    FROM branch 
                    WHERE city='LA') 
EXCEPT (SELECT C.branch 
        FROM accountholder AS B, account AS C 
        WHERE B.accountnumber = C.accountnumber 
              AND A.customId = B.customId));

Now I got:

ERROR 1064 (42000): 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 'EXCEPT (SELECT C.branch FROM accountholder AS B, account AS C WHERE B.accountnumber=' at line 1

And I do not see the problem. Am I blind or just stupid?

Thanks for help.

Upvotes: 3

Views: 6468

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562270

As others have stated, MySQL does not support the EXCEPT operation. You will have to rewrite your query.

You appear to be querying accountholders if there is no other branch in LA besides those branches at which the accountholder's accounts are held.

Here's a rough guess:

SELECT A.customId
FROM accountholder AS A
JOIN account AS C 
  ON A.accountnumber = C.accountnumber
LEFT OUTER JOIN branch AS B
  ON C.branch <> B.name AND B.city = 'LA'
WHERE B.city IS NULL;

I'm making some assumptions about your tables and columns and their relationships, so this query is just a guess. Do not just run it blindly and expect it to work. I ask you to use it as an example and confirm that the comparisons are being done correctly for your data.

Upvotes: 2

Hartmut Holzgraefe
Hartmut Holzgraefe

Reputation: 2765

MySQL only supports UNION but not INTERSECT and EXCEPT/MINUS

Adding these set operations has been a long standing feature request

http://bugs.mysql.com/bug.php?id=1309

You may want to vote "Affects me" on that bug report ...

Upvotes: 1

John Conde
John Conde

Reputation: 219804

MySQL does not use EXCEPT. Use NOT IN.

SELECT A.customId 
FROM accountholder as A 
WHERE branch NOT IN ( 
(SELECT name FROM branch WHERE city='LA') 
AND branch NOT IN (SELECT C.branch FROM accountholder AS B, account AS C WHERE B.accountnumber = C.accountnumber AND A.customId = B.customId));

Upvotes: 7

Related Questions