CODEWITHSUNDEEP
mysqlsql
Marko Paju
Marko Paju

Reputation: 312

SQL table where clause error

i need the LAST_NAME, JOB_ID, SALARY columns from table workers and the job id needs to be SH_CLERK or SA_REP and the SALARY column CANNOT equal to 1000,2600,3000,7000,8000 or 9000

SELECT `LAST_NAME`, `JOB_ID`, `SALARY` FROM `workers`
WHERE `JOB_ID` = SH_CLERK OR SA_REP AND `SALARY` != 1000 and 2600 and 3000 and 7000 and 8000 and 9000;

i dont know whats wrong, i get the #1054 - Unknown column 'SH_CLERK' in 'where clause' error.

Thanks !

Upvotes: 1

Views: 124

Answers (3)

472084
472084

Reputation: 17885

Put quotes around strings.

Switch multiple OR's to using IN instead.

SELECT LAST_NAME, JOB_ID, SALARY
  FROM workers
 WHERE JOB_ID IN ('SH_CLERK', 'SA_REP')
   AND SALARY NOT IN (1000, 2600, 3000, 7000, 8000, 9000);

Upvotes: 1

Charlesliam
Charlesliam

Reputation: 1313

Assuming if I understand the problem correctly, If job_id is a number data and SH_CLERK or SA_REP is taken from other table.

`Select last_name, job_id, salary 
 from workers 
 where job_id = (select id from <user table> 
                 where <sh_clerk/sa_rep column name field> like 'SH_CLERK' or 
                       <sh_clerk/sa_rep column name field> like 'SA_REP')
 and SALARY not in(1000, 2600, 3000, 7000, 8000, 9000);`

<user table> is the table where SH_CLERK or SA_REP taken from

<sh_clerk/sa_rep column name field> is the column name in the <user table>

Upvotes: 0

Hogan
Hogan

Reputation: 70513

SELECT LAST_NAME, JOB_ID, SALARY
FROM workers
WHERE  JOB_ID IN ('SH_CLERK', 'SA_REP') AND
   SALARY NOT IN (1000, 2600, 3000, 7000,8000, 9000)

Upvotes: 0

Related Questions