CODEWITHSUNDEEP
mysqlsql
Max Z
Max Z

Reputation: 107

Using EXCEPT operator on MySql 5.1 version

I have 5.1 MySQL version on my server. I am trying to perform this query:

SELECT File_Name
FROM Words_DB
WHERE Word_Name=" . $element . "
EXCEPT 
SELECT File_Name 
FROM Files_DB 
WHERE Display=0

I am getting an error:

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 'EXCEPT SELECT File_Name FROM Files_DB WHERE Display=0' at line 4

Can someone tell me how can i perform this query in an alternative form?

Thank you, Max.

Upvotes: 4

Views: 19374

Answers (2)

Aniket Ghodke
Aniket Ghodke

Reputation: 9

I think you can find better answers on the following site: http://www.tutorialspoint.com/sql/sql-except-clause.htm

It says you can use except query. But you can also use answer provided by JPW above that instead of using except you can use NOT IN key word which works in the same way.

Upvotes: -2

jpw
jpw

Reputation: 44881

As far as I know MySQL does not support theEXCEPToperator. Try this instead:

SELECT File_Name
FROM Words_DB
WHERE Word_Name=" . $element . "
AND File_Name NOT IN (
  SELECT File_Name 
  FROM Files_DB 
  WHERE Display=0
)

You could also use either a correlatedNOT EXISTSor aLEFT JOIN. As I don't use MySQL much I can't say which performs best.

Upvotes: 6

Related Questions