Getting result from select command [SQL]

Question

This is my table:

INSERT INTO Emp (EmpNo, EName, Job, Mgr, HireDate, Sal, Comm, DeptNo) 
VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20), 
       (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30), 
       (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30), 
       (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20), 
       (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30), 
       (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30), 
       (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10), 
       (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20), 
       (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10), 
       (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30), 
       (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20), 
       (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950,NULL, 30),
       (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
       (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

Now I want to get every worker who their job isn't PRESIDENT or MANAGER.

This is my command:

SELECT * 
FROM [dbo].EMP 
WHERE NOT Job = 'MANAGER' OR Job = 'PRESIDENT';

The result is almost correct, but I get one PRESIDENT who shouldn't be there.

This one:

7839    KING    PRESIDENT   NULL    1981-11-17  5000.00 NULL    10

What rdbms you are using?

dwir182
– dwir182

2018-10-20 07:41:56 +00:00
Commented Oct 20, 2018 at 7:41 — dwir182
– dwir182, Commented Oct 20, 2018 at 7:41

Lukasz Szozda · Accepted Answer · 2018-10-20 07:41:06Z

5

You need to wrap it with brackets:

SELECT * FROM [dbo].EMP WHERE NOT (Job='MANAGER' OR Job='PRESIDENT');

Or use NOT IN:

SELECT * FROM [dbo].EMP WHERE Job NOT IN ('MANAGER', 'PRESIDENT');

answered Oct 20, 2018 at 7:41

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ilyes · Accepted Answer · 2018-10-20 13:59:22Z

0

Use NOT IN

SELECT * FROM [dbo].EMP WHERE Job NOT in  ('MANAGER' ,'PRESIDENT')

edited Oct 20, 2018 at 13:59

Ilyes

15k5 gold badges31 silver badges61 bronze badges

answered Oct 20, 2018 at 7:42

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Collectives™ on Stack Overflow

Getting result from select command [SQL]

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related