CODEWITHSUNDEEP
mysql
Steve Farmer
Steve Farmer

Reputation: 35

MySQL Minus query syntax

I am working on a query that will pull information from one part of the database but remove a part of the result. This is the database:

CREATE TABLE elements 
(
   atomicNumber INT UNSIGNED NOT NULL PRIMARY KEY,
   symbol VARCHAR(3) NOT NULL,
   elementName VARCHAR(25) NOT NULL,
);    

INSERT INTO elements VALUES (1, 'H', 'Hydrogen');
INSERT INTO elements VALUES (2, 'He', 'Helium');
INSERT INTO elements VALUES (3, 'Li', 'Lithium'); 
INSERT INTO elements VALUES (4, 'Be', 'Beryllium');
INSERT INTO elements VALUES (5, 'B', 'Boron');
INSERT INTO elements VALUES (6, 'C', 'Carbon');
INSERT INTO elements VALUES (7, 'N', 'Nitrogen');
INSERT INTO elements VALUES (8, 'O', 'Oxygen');
INSERT INTO elements VALUES (9, 'F', 'Fluorine');
INSERT INTO elements VALUES (10, 'Ne', 'Neon');

I want to return all the records in the database other than atomicNumber 1. This is the query I use:

Select atomicNumber, symbol, elementName From elements Minus select atomicNumber, symbol, elementName Where atomicNumber = 1;

When I do that query, I get this error:

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 'Minus....

What is my error in the query syntax? I have looked around for the last few hours but cannot figure out what I am doing wrong.

Upvotes: 0

Views: 924

Answers (2)

Chris Forrence
Chris Forrence

Reputation: 10104

To exclude different elements, you can just use the not-equals comparison:

SELECT * FROM elements WHERE atomicNumber != 1;

If you have many elements that you'd like to exclude, you can use NOT IN instead (but it may not be very performant):

SELECT * FROM elements WHERE atomicNumber NOT IN (2,5,10);

Upvotes: 1

Imanol Valiente Martin
Imanol Valiente Martin

Reputation: 11

From? on the second query

Select atomicNumber, symbol, elementName From elements Minus select atomicNumber, symbol, elementName Where atomicNumber = 1;

Select atomicNumber, symbol, elementName FROM elements Where atomicNumber = 1;

I guess..

I mean, you are missing a From statement, just that, review your code.

Upvotes: 0

Related Questions