SQL query operator minus is not working

php mysql except
vankraster picture vankraster · Jan 21, 2011 · Viewed 7.5k times · Source 
(SELECT IDOperatore FROM operatore) MINUS 
(SELECT IDOperatore FROM commessaoperatore GROUP BY IDOperatore)

This query is no working, even if I try to replace MINUS with EXCEPT. The singular query SELECT IDOperatore FROM operatore and SELECT IDOperatore FROM commessaoperatore GROUP BY IDOperatore are working, but if I try to put together with Minus operator they don't work.

ERROR: #1064 - 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 'MINUS (SELECT IDOperatore FROM commessaoperatore GROUP BY IDOperatore)' at line 1

Answer

Michael Pakhantsov picture Michael Pakhantsov · Jan 21, 2011

MINUS does not exists in mysql

however for you query you can use NOT EXISTS:

SELECT IDOperatore FROM operatore o
WHERE NOT EXISTS (SELECT 1
                 FROM commessaoperatore c 
                 WHERE c.IDOperatore = o.IDOperatore)

Related questions

How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
Import file size limit in PHPMyAdmin

I have changed all the php.ini parameters I know: upload_max_filesize, post_max_size. Why am I still seeing 2MB? Im using Zend Server CE, on a Ubuntu VirtualBox over a Windows 7 host.

Read More
PDOException “could not find driver”

I have just installed Debian Lenny with Apache, MySQL, and PHP and I am receiving a PDOException could not find driver. This is the specific line of code it is referring to: $dbh = new PDO('mysql:host=' . DB_HOST . …

Read More